Relational Databases - Lecture 3 - Logical and physical models (UML)
0 likes91 views
The document describes logical and physical data models using UML notation. It shows various entity relationships like one-to-one, one-to-many, many-to-many relationships. It also covers advanced concepts like self-relationships, data integrity constraints and how the logical model maps to a relational schema and physical model.
Relational Databases - Lecture 3 - Logical and physical models (UML)
- 2. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Logical model Represents Entities Relationships and cardinalities Attributes Primary keys Foreign keys StudentID (PK) Name Surname Nationality UniversityName (FK) STUDENT UniversityName (PK) Country Address UNIVERSITY 1-1 1-N
- 3. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Logical model Student UniversityAttends Name Surname Nationality Name Address Country StudentID StudentID (PK) Name Surname Nationality UniversityName (FK) STUDENT UniversityName (PK) Country Address UNIVERSITY 1-1 1-N
- 4. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Week entity BuildingIDHeight Building containscontains Color NumFloors Apartment Number Floor Size Number (PK) BuildingID (PK) (FK) Size Floor APARTMENT BuildingID (PK) Color Height NumFloors BUILDING 1-1 1-N
- 5. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Many-to-many Student Class Completion StudentID ClassID containsachieves containscontains GradeSemester StudentID (PK) STUDENT ClassID (PK) CLASS 1-1 0-N StudentID (PK) ClassID (PK) (FK) Semester (PK) (FK) Grage COMPLETION 1-1 1-N
- 6. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Self-relationship University Name Address Country Branch UniversityName (PK) Country Address Branch of (FK) UNIVERSITY Branch UniversityName (PK) Country Address UNIVERSITY BranchName (PK) UniversityName (FK) UNIVERSITY BRANCH
- 7. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Data integrity Primary key is always unique A primary key cannot be deleted if there is still a foreign key that refers to this primary key. Student or class tuple cannot be deleted if a tuple in COMPLETION table referring to them exists StudentID (PK) STUDENT ClassID (PK) CLASS 1-1 0-N StudentID (PK) ClassID (PK) (FK) Semester (PK) (FK) Grage COMPLETION 1-1 1-N
- 8. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Relational schema STUDENT (studentID, name, surname, nationality, universityName) (N,1) UNIVERSITY (universityName, country, address) StudentID (PK) Name Surname Nationality UniversityName (FK) STUDENT UniversityName (PK) Country Address UNIVERSITY 1-1 1-N
- 9. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Physical model Represents Relationships Primary keys Foreign keys Table names Column names Column constraints Column data types STD_ID: INTEGER UNV_NAME: VARCHAR (50) STD_NAME: VARCHAR (30) STD_SURNAME: VARCHAR (30) STD_NAT: VARCHAR (20) STUDENT UNIVERSITY UNV_NAME: VARCHAR (50) UNV_COUNTRY: VARCHAR (20) UNV_ADDRESS: VARCHAR (90)
- 10. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Self-relationship University Name Address Country Branch BRANCHUNIVERSITY UNV_NAME: VARCHAR (50) UNV_COUNTRY: VARCHAR (20) UNV_ADDRESS: VARCHAR (90) UNV_SATELLITE: VARCHAR (50) UNV_HOME: VARCHAR (50)
- 11. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Data types/domain VARCHAR (30) BOOLEAN INTEGER DECIMAL(2,3) FLOAT DATE TIME TIMESTAMP etc.
- 12. 1 0 1 0 0 1 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1 1 1 0 0 1 0 1 0 0 1 1 0 0 1 1 1 0 0 0 1 0 1 1 1 1 1 0 0 1 0 1 0 0 1 0 1 0 0 1 1 0 1 0 1 1 0 1 1 0 0 1 1 1 0 1 Summary Conceptual Logical Physical Entity Names Relationships Attributes Primary keys Foreign keys Table names Column names Data types