dbms conceptual modeling 2, Lecture notes of Database Management Systems (DBMS)

Download dbms conceptual modeling 2 and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity! Logical Design of the Database THE RELATIONAL MODEL Requirement Gathering and Analysis Know what user want and what will be stored in the database Conceptual Design Visualize the conceptual design of the database using Entity Relationship Model Logical Design Logical Database representation using Relational Model Physical Design Physical database design using SQL and Implementation using a DBMS Relational Model Terminology cont... Domain • A domain is the set of allowable values for a attribute • The special value null is a member of every domain Relational Schema • A relation schema describes the relation name (table name), attributes, and their names. Definition Summary Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Values Domain Row Tuple Table Definition Schema of a Relation Populated Table State of the Relation Basic Structure Example: ◦ name = {Jones, Smith, Paul, Lindsay, …} ◦ street = {Main, North, Park, …} ◦ city = {London, Southampton, Liverpool, …} Then Relation is = { (Jones, Main, London), (Smith, North, Southampton), (Paul, North, Liverpool),(Lindsay, Park, London) } We can get this by a cartesian product of name x street x city Properties of Relations Each cell of the relation contains exactly one atomic (single) value The values of an attribute are all from the same domain Each tuple is distinct. There are no duplicate tuples The order of attributes has no significance The order of tuples has no significance, theoretically. (However, in practice, the order may affect the efficiency of accessing tuples.) Order of Attributes and Tuples Attrib1 Attrib2 Attrib3 X Y Z A B C Attrib2 Attrib1 Attrib3 Y X Z B A C Attrib3 Attrib1 Attrib2 Z X Y C A B Attrib1 Attrib2 Attrib3 A B C X Y Z Attrib2 Attrib1 Attrib3 B A C Y X Z Attrib3 Attrib1 Attrib2 C A B Z X Y Relational Model Integrity Constrains Constraints are conditions that must hold on all valid relation states. There are three main types of constraints in the relational model: ◦ Key constraints ◦ Entity integrity constraints ◦ Referential integrity constraints Another implicit constraint is the domain Integrity constraint ◦ Domain integrity means the definition of a valid set of values for an attribute. ◦ Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute) Key Constraints (continued) If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. ◦ The primary key attributes are underlined. Example: Consider the STUDENT relation schema: ◦ STUDENT(Index Number, Name, Birthday,NIC, Academic Year) ◦ We chose Index as the primary key The primary key value is used to uniquely identify each tuple in a relation ◦ Provides the tuple identity Also used to reference the tuple from another tuple ◦ General rule: Choose as primary key the smallest of the candidate keys (in terms of size) ◦ Not always applicable – choice is sometimes subjective COMPANY Database Schema EMPLOYEE Fname | Minit | Lname | Ssn | Bdate | Address | Sex | Salary | Super_ssn| Dno DEPARTMENT Dname | Dnumber | Mgr_ssn | Mgr_start_date DEPT_LOCATIONS Dnumber | Dlocation PROJECT Pname | Pnumber | Plocation Dnum WORKS_ON Esen | Pao. | Hours Figure 5.5 Schema diagram for DEPENDENT the COMPANY relational database schema. Essn Dependent_name | Sex | Bdate Relationship Entity Integrity Entity Integrity: ◦ The primary key attributes PK of each relation schema R in S cannot have null values in any tuple. ◦ This is because primary key values are used to identify the individual tuples. ◦ t[PK]  null for any tuple t in r(R) ◦ If PK has several attributes, null is not allowed in any of these attributes ◦ Note: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key. Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema. EMPLOYEE Fname | Minit | Lname | Ssn | Bdate | Address | Sex | Salary | Super_ssn | Dno — Dname | Dnumber | Mgr_ssn | Mgr_start_date tH DEPT_LOCATIONS Dnumber | Dlocation LS PROJECT Pname | Pnumber | Plocation Dnum tL WORKS_ON Essn Pno Hours | Co DEPENDENT Essn Dependent_name | Sex | Bdate Relationship l In-Class Exercise Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(Index Number, Name, Major, Bdate) COURSE(Course Code Cname, Dept) ENROLL(SSN, Course Code, Semester, Grade) BOOK_ADOPTION(Course Code, Semester, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema.