Data Modeling and Relational Database Design ISYS 650
Data Modeling and Relational Database Design ISYS 650
Conceptual Database Design Methodology • Identify entity types. • Identity relationship types between the entity types. • Identify and associate attributes with entity or relationship types. • Determine attribute domains. • Determine candidate keys and primary key. • Validate conceptual model: – Check for redundancy, support required transactions, review the model with user
Entity Type • A collection of entities that share common properties or characteristics. • An entity type represents a collection of entities. • In an ERD, it is given a singular name. • Diagrammatic representation: – A rectangle labeled with the name of the entity
Relationship Type • Relationship: Interaction between entity types. – It is an association representing an interaction among the instances of one or more entity types that is interest to the organization. • It has a verb phrase name: – Faculty teach Course, Faculty advise Student – Customer open Account, Customer purchase Product.
Binary Relationship types and instances a) Relationship type b) Relationship instances
Binary Relationship • A relationship involves two entity types. • Three kinds of Binary Relationship – 1: 1 – 1: M – M: M
Interpretation of an M: M Relationship Boy Peter Mary Paul Linda John Nancy Woody Mia Alan Pia A boy may date 0, 1, or many girls. A girl may date 0, 1, or many boys. Note: “Many boys date many girls” is not a correct interpretation. Girl
Cardinality Constraint • A cardinality constraint specifies the number of instances of entity type A that can (or must) be associated with each instance of entity type B. • Participation constraint – Full participation (Mandatory) – Partial participation (Optional)
Notations
Other Notations UML Notations: – 0. . 1, 1. . 1 – 0. . *, 1. . * – 3. . 5 Student Has 1. . 1 Account • Traditional: Student 1 Has 1 Account
Traditional ERD Notations 1 Student M M Advise Has 1 Account M Enroll 1 Faculty 1 Teach M Course
UML ERD Notations Student Has 1. . 1 0. . * Advise 1. . 1 Account Enroll 0. . * 1. . 1 Teach Faculty 1. . 1 1. . * Course
Other Notations Has Student Account Enroll Advise Faculty 0. . * Teach Course
Recursive Relationship • A relationship type where the same entity type participates more than once in different roles. • Examples: – Employee – Supervise -- Employee – Student -- Tutor– Student – Faculty – Evaluate -- Faculty
Supervise Supervisor Employee Supervisee Employee 1 M Supervise
Attributes • Properties of an entity or a relationship. • Simple and composite attributes – Address: Street address, City, State, Zip. Code – Street Address: Number, Street, Apt# – Phone#: Area Code, number • Single-valued and multi-valued attributes – – Student’s Major attribute Faculty’s Degree. Earned attribute Vehicle’s Color attribute Others: Phone. Number, Email. Address • Derived attributes • Keys: Key attribute uniquely determines an entity. – Candidate key, primary key, composite key
UML Notations Student SID {PK} Sname Fname Lname Address Street City State Zip Phone[1. . 3] Sex Date. Of. Birth /Age
SID {PK} Sname( Fname, Lname) Address( Street, City, State, Zip) {Phone} Sex Date. Of. Birth [Age]
Fname SID Lname Sname Phone Date. Of. Birth Age Student
Attributes on Relationship Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart M Has M Product Price PID Pname
Order Form
Online Shopping Cart CID Addr Cname Customer 1 Has Cart. ID M Date Shopping. Cart M Qty Has M Product Price PID Pname
Attributes on Relationship • Examples: – Student/Course: Grade – Order/Product: Quantity
N-ary Relationship • Doctor – Patient – Ailment • Police – Criminal – Crime • Air. Craft – Bomb – Target • Note: There is no deterministic relationship (1: 1 or 1: M) between any two of these entities.
Examples of relationships of different degrees (cont. ) c) Ternary relationship Note 1: a relationship can have attributes of its own. Note 2: This ternary relationship exists only if there is no binary relationship between these three entities.
Examples of multiple relationships Employees and departments Entities can be related to one another in more than one way Example: Auction site: User and Auction Item
Properties of a Relation • Simple attribute – No composite, no multivalued attribute • Each relation must have a primary key: – Simple or composite key – May have other keys (candidate keys) – Key cannot be null – Cannot be duplicated
Integrity Constraints • Domain constraints • Entity integrity: – Primary key cannot be null, cannot be duplicated • Referential integrity • Other constraints
Relational Database Design • Entity: Create a table that includes all simple attributes – Composite • Multi-valued attribute: Create a table for each multivalued attribute – Key + attribute • Relationship: – 1: 1, 1: M • Relationship table: for partial participation to avoid null values • Foreign key – M: M: relationship table – N-ary relationship: relationship table – Recursive relationship • Attribute of relationship
Online Shopping Cart CID Email. Addr Cname Customer 1 Has Cart. ID Date M Shopping. Cart M Has Qty M Product PID Price Pname
Recursive Relationship Note: Partial participation
Ternary Relationship
Third Normal Form • The database designed according to these rules will meet the 3 NF requirements.
Normalized Database • A normalized database is a database where in each relation the non key fields are functionally dependent on the key, the whole key, and nothing but the key.
Function Dependency • Relationship between attributes • X -> Y – The value of X uniquely determines the value of Y. – Y is functionally dependent on X. – A value of X is associated with only one value of Y.
Example • Employee table: – – SSN S 1 S 2 S 3 Ename Peter Paul Mary Sex M M F DOB 1/1/75 12/25/80 7/4/72 • Function Dependencies: – SSN -> Ename, SSN ->Sex, SSN -> DOB – SSN -> Ename, Sex, DOB • Any other FD: – Ename -> SSN ? – Ename -> Sex ? – DOB -> SSN ?
• What is the key of Employee table: – SSN • Observations: – – All non-key fields are functionally dependent on SSN. There is no other FD. The only FD is the key dependency. There is no data duplication in the Employee table.
If we mix a multivalue attribute with regular attributes in one table • Employee Table: – SSN, Ename, Sex, DOB, Phone – Assuming an employee may have more than 1 phone. • Key: SSN or SSN + Phone • Duplication ? • Problem: Some non-key fields depend on part of the key – SSN + Phone -> Ename, Sex, DOB – SSN -> Ename, Sex, DOB
If we mix two entities with M: M relationship in one table • Student. Course table: – SID, Sname, GPA, CID, Cname, Units • Key: SID + CID • Duplication? • Problem: Some non-key fields depend on part of the key – SID+CID -> Sname, GPA, Cname, Units – SID -> Sname, GPA – CID-> Cname, Units
If we mix two entities with 1: M relationship in one table • Faculty. Student table: – Faculty Advise Student: 1: M relationship – FID, Fname, SID, Sname, SAddress • Key: SID • Duplication? • Problem: Not nothing but the key – SID -> FID, Fname – FID -> Fname
Third Normal Form • Every non-key field is: – Fully functionally dependent on the key, and – Not transitively dependent on the key. • The database does not have duplication due to mixing: – Multi-value attribute with single-value attributes – Entity types with relationship
- Slides: 41