ZEIT2301 Design of Information Systems Relational Database Schema

1. ZEIT2301Design of Information SystemsRelational Database Schema School of Engineering and Information Technology UNSW@ADFA Dr Kathryn Merrick

2. Topic 09: Relational Database Schema • Objectives • To study relational database schema in practice • To study the conversion between ER diagrams and relational database schema • We will also start to look at MS Access today

3. Relational Data Model (Review) Identifies entities, attributes and relationships. Is the theoretical basis for Relational Database Management Systems Dominant model for data-processing in use in enterprises today Data is organised into tables (relations), with columns (attributes) and rows (records) Can be accessed in any sorted order Doesn’t have hidden pointers to connect entities - just uses data Any entity can be connected to any other entity by using data Relational model is therefore VERY flexible 3

4. A Relation (table) Relation or table name Attributes or columns Row: an entity instance 4

5. Missing (Null) Values • Null means “no value/unknown”, and is not the same as zero, blank or an empty string Null 5

6. Primary Key • Every row in a table must be distinguishable from every other row • Must have a PK Primary Key 6

7. Primary Key Primary key (table in “design view” in MsAccess) 7

8. A Composite Key Compositekey Session 2, 2010 8

9. Foreign Keys A foreign key (FK) is a “copy” of a primary key that has been exported from one table and added as a new column in another table to represent the relationship between them A foreign key is a copy of the whole of its parent primary key if the primary key is composite then so is the foreign key Foreign keys are crucial in the relational model (and consequently in relational databases) They are the ‘glue’ that connects the relations in the database 9

10. Foreign Key - Example Foreign Key Textbook Edition Publisher ID ISBN Title 0-201-34287-1 Database Systems 2 P091 0-02-366881-4 P473 Database Processing 5 null Analysis and Design 0-202-36995-4 P091 Primary Key Publisher Name Primary Key Publisher ID Longman P091 University Press P473 10

11. A Foreign Key in MSAccess Primary key of Lecturer table Primarykey ofCourse table Foreignkey NB. The name of the FK attribute does not need to match the PK – but the data type should be the same. 11

12. Foreign Keys & Referential Integrity A foreign key can only take on a value that matches a valid value in its parent primary key, or (possibly) be null A database in which all foreign keys contain such matching values is said to exhibit referential integrity Helps ensure database consistency if the DBMS enforces referential integrity

13. Referential Integrity Constraint Name Publisher ID Longman P091 University Press P473 Foreign Key Textbook Edition Publisher ID ISBN Title 0-201-34287-1 Database Systems 2 null P473 0-02-366881-4 Database Processing 5 null Analysis and Design 0-202-36995-4 P807 Violates referential integrity Publisher Primary Key

14. Referential Integrity Constraint Foreign Key Course Credits Lecturer ID Level Name Undergrad Database Systems 3 38421 Undergrad null Data Networks 6 null Speech Processing Postgrad 38421 Primary Key Note: Foreign Key attribute name not necessarily the same as primary key Lecturer StaffName Primary Key Staff ID Goscinski 91027 Nguyen 38421 14

15. Relation Schema The Schema for a relation represents its structure. Single table example: Book (ISBN, Title, Edition, Pages) where Book is the name of the relation and “ISBN, Title, Edition and Pages” is the unordered list of its attributes Primary Key is underlined and, by convention, shown first

16. Relational Database Schema The complete design of a database is termed its schema ARelational Database Schema consists of a number of relation (tables) Table attributes and PKs are listed Foreign keys that link the tables, are identified either by arrows (see next slide) or by a textual description of the links 16

17. A Relational Database Schema primary key lecturer (lecturerName, school, address, telephone, title) referential integritylink contact (lecturerName, courseCode, hours) table (relation) name course (courseCode, courseName, lecturerName) foreign key enrol (studentID, courseCode, mark) attribute name(s) student (studentID, name, DOB, address, telephone, gender, degree) 17

18. Summary of key features of the Relational Model In the relational model data is stored in relations, represented as tables with columns and rows. Columns represent attributes Rows represent entity instances Primary Keys uniquely identify each row. Foreign Keys provide the link between tables. 18

19. Converting ER diagrams to a Relational Schema Once an ER model has been developed it needs to be converted into a “relational schema” A relational schema is a specification of the required table definitions and their foreign key links The basis for design of a relational database There are well-defined principles for converting from one to the other 19

20. Conversion Rules for Entities Each entity becomes a relation (table) Each single-valued attribute of the entity becomes a column (attribute) of the table representing the entity Composite attributes are represented only by their components Derived attributes are ignored (record in data dictionary) ER key primary key 20

21. Entity Example Relation/table: player(playerID, name, DOB, height, weight, gender) 21

22. In-class exercise: Derive the relational schema

23. Solution car (regoNo, make, model, year, engineCapacity)

24. In-class exercise: Derive the relational schema

25. Solution Derived attribute ‘age’ not included in relational schema. Composite attribute ‘address’ not included (only component parts) player(playerID, name, DOB, street, suburb, postcode) 25

26. Multivalued Attributes Multivalued attributes are not dealt with by having repeating columns in the table. That is: should not be represented by: person (... qual1, qual2, qual3, ... ) 26

27. Multivalued Attributes The correct way to represent multivalued attributes is with another table Example: • person (personID, name… , address… , ...etc...) • personQual (personID, qualification) Note: Composite PK consists of PK from original table plus the multi-valued attribute 27

28. In-class exercise: Derive the relational schema

29. tastingNote (wineID, tastingNote) wine (wineID wineName, year, producer, winemaker, type) winePrize (wineID,prize) Solution

30. Representing Relationships How an ER relationship is represented depends on the degree (unary, binary, ternary) of the relationship and its multiplicity We consider binary relationships here Three possible multiplicities are: one-to-one 1:1 one-to-many 1:* many-to-many *:* Note: * includes zero

31. 1:1 Relationships To represent a 1:1 relationship, a foreign key is migrated from either relation into the other - but not both ways Which direction is chosen generally depends on how the connected entities participate in the relationship If the relationship itself has attributes, those attributes are included in the relation that contains the foreign key

32. 1:1 Relationship – Schema 0..1 1..1 is head of Could be represented by the schema: staff (staffID, name, ...etc... ) department (deptName, location, deptHead) To implement the relationship, staffID is migrated to the department relation as a FK called deptHead. 32

33. 1:1 Relationships The 1:1 relationship could also be represented by the schema: staff (staffID, name, ..... , headOfDept) department (deptName, ...etc...) To implement the relationship, deptName is migrated to the staff relation, as a FK called headOfDept. The first option (previous slide) is better because all departments have heads but few staff are heads of departments

34. 1:* Relationships Convert entities into relations (tables) Include the primary key from the ‘one’ side relation as a foreign key in the ‘many’ side relation Include attributes of the relationship, if any, in the relation containing the foreign key 34

35. 1:* Relationship - schema 1..* 1..1 has Migrate the PK from the one side of the relationship as a Foreign Key in the many side. club(clubName, contactNo) team(teamName, grade, clubName) 35

36. *:* Relationships Represented by a new table (often called an associative relation) New table contains two foreign keys - one from each of the participants in the relationship The PK of the new table is a composite of the two foreign keys Attributes of the relationship, if any, become attributes of the new table

37. *:* Relationship - schema 0..* 1..* playsIn player(playerID, name, DOB, ….. ) New table (from *..* relationship) playsIn(playerID, teamName) team(teamName, grade)

38. *:* Relationship with an attribute mark 1..** 1..* enrols In student (studentID, name, ...etc...) New table (from *..* relationship) enrolment (studentID, courseCode, mark) course (courseCode, name, ...etc...) 38

39. In-class exercise: Convert to relational schema 0..* tiedTo 0..1 0..* ownedBy 1..* Session 2, 2010 39

40. In-class exerciseStep 1: Create tables for entities yacht(yachtNo, name, length, breadth, depth, ….) berth(jetty, berthNo, length, depth, ….) member(memberNo,name, address, phoneNo, ….)

41. In-class exerciseStep 2: Implement 1:* relationship yacht(yachtNo, yachtName, length, breadth, depth, jettyTied, berthTied) berth(jetty, berthNo, length, depth, ….) member(memberNo, name, address, phoneNo, ….)

42. In-class exerciseStep 3: implement *:* relationship yacht(yachtNo, yachtName, length, breadth, depth, jettyTied, berthTied) berth(jetty, berthNo, length, depth) ownedBy(yachtNo, memberNo) member(memberNo, name, address, phoneNo)