Relational Database Concept I
Class work: P56 - 57
Customer Rent DVD
Complete the data dictionary of the tables.
Customer
Dvd
Transaction
Draw the schemas
Write SQL to create the table (Consider the constraint needed)
How the 3 tables "Link" together
where clause (Filtering condition) and (join condition)
Draw the schema of the database on P.59-60
3.3 Relational Database terminology
Entity (e.g. CUSTOMER, STUDENT, CAR, DVD,SONG) - implement by a table.
Attribute (Fields)
Domain (Data type / allowable data)
Tuple (Record)
Relationship (Join condition)
Schema
Candidate Key
Primary Key
Foreign key
Composite key
Index
3.4 Integrity Constraints
Entity integrity (check primary key: unique and not dull)
Referential Integrity (Check foreign Key)
Domain Integrity: validation of data, data type
Other constraints:
Reasonable check across fields.
e.g. Departure time must be early than arrival time.
SQL Exercise 5 (Worksheet will be distributed in class)
Data:
INSERT INTO CD VALUES ('0001', 'Spring 08');
INSERT INTO CD VALUES ('0002', 'Summer 08');
INSERT INTO CD VALUES ('0003', 'Autumn 08');
INSERT INTO CD VALUES ('0004', 'Winter 08');
INSERT INTO Song VALUES ('173', 'Tree and Leaves', '0001', '121');
INSERT INTO Song VALUES ('174', 'Leaves and Flowers', '0001', '131');
INSERT INTO Song VALUES ('175', 'Rose', '0001', '121');
INSERT INTO Song VALUES ('176', 'Green land', '0001', '131');
INSERT INTO Song VALUES ('177', 'Livid songs', '0002', '121');
INSERT INTO Song VALUES ('178', 'Speed', '0002', '131');
INSERT INTO Song VALUES ('179', 'Power', '0002', '141');
INSERT INTO Song VALUES ('180', 'Non-stop', '0002', '141');
INSERT INTO Song VALUES ('181', 'Calm lives', '0003', '121');
INSERT INTO Song VALUES ('182', 'Staying still', '0003', '151');
INSERT INTO Song VALUES ('183', 'Beautiful sun', '0003', '151');
INSERT INTO Song VALUES ('184', 'White snow', '0004', '131');
INSERT INTO Song VALUES ('185', 'Strong wind', '0004', '131');
INSERT INTO Song VALUES ('186', 'Good sleep', '0004', '121');
INSERT INTO Artist VALUES ('121', 'Ricky Tham');
INSERT INTO Artist VALUES ('131', 'Tony Chen');
INSERT INTO Artist VALUES ('141', 'Fanny Law');
INSERT INTO Artist VALUES ('151', 'Jenny Lee');