Chapter 5 Database Design Lecture 10 1 Main

Chapter 5 Database Design Lecture 10 1

Main Topics • • • 2 Database Design Conceptual Database Design E-R Diagram Entities Attributes Relationships

Database Design • A database is a collection of data that is organized so that its contents can easily be accessed, managed, and updated. There are three main phases of Database Design 1. Conceptual Database Design 2. Logical Database Design 3. Physical Database Design 3

Database Design 1. Conceptual Database Design: - Highest level design - Identification of entities, attributes, and relationships - Independent of all physical considerations. - Uses ER model 4

Database Design 2. Logical Database Design: - Translation of ER diagram to a relational database schema 3. Physical Database Design: - Decide how the logical structure is to be physically implemented (as relations) in the target DBMS. - Actual computer implementation. 5

Conceptual Database Design • Most organizations today do conceptual database design using E-R modeling. • Entity-Relationship data model (E-R model): a detailed, logical representation of the entities, associations and data elements for an organization or business area. 6

E-R Diagram • Entity-relationship diagram (E-R diagram): a graphical representation of an E-R model • The E-R diagram is use the following notation : – Data entities. – Attributes – Relationships 7

Entities • Entity: a person, place, object, event or concept in the user environment about which data is to be maintained. - An entity is described using a set of attributes. • Entity set: collection of entities that share common properties or characteristics E. g. , all employees. - All entities in an entity set have the same set of attributes. • Entity instance: single occurrence of an entity set 8

Entities • • • Person: EMPLOYEE, STUDENT, PATIENT Place: STATE , STORE, WAREHOUSE. Object: MACHINE, BUILDING, CAR Event: SALES, REGISTRATION, RENEWAL Concept: ACCOUNT, COURSE, WORK CENTER 9

Attributes • Attribute: a property or characteristic of an entity possessed by all members of an entity set. Example: customer = (customer-id, customer - name, customer-street, customer-city) loan = (loan-number, amount) • Domain – the set of permitted values for each attribute. 10

Attributes • Candidate key: an attribute (or combination of attributes) that uniquely identifies each instance of an entity set. • Identifier (Primary key): a candidate key that has been selected as the unique, identifying characteristic for an entity set. 11

Relationships • Relationship: an association between the instances of one or more entity sets. since name ssn salary Employees dname did Works_In budget Departments Relationships can have attributes (extra attributes for the relationship) For example, the above relationship 12

Degree of Relationships • Degree of a relationship is the number of entity sets that participate in a relationship. Unary Relationship Binary Relationship Ternary Relationship 13

Relationship Degree • Unary relationship: a relationship between the instances of one entity set – Also called a recursive relationship • Binary relationship: a relationship between instances of two entity sets – Most common type of relationship encountered in data modeling • Ternary relationship: a relationship among instances of three entity sets. 14

Degree of a relationship is the number of entity sets that participate in it One entity instance related to another of the same entity set Entities of two different sets related to each other Entities of three different sets related to each other 15

Cardinality of Relationships • One-to-One – Each entity in the relationship will have exactly one related entity • One-to-Many – An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity • Many-to-Many – Entities on both sides of the relationship can have many related entities on the other side 17

18

19

20

21

E-R Diagrams ØRectangles represent entity sets. ØDiamonds represent relationship. ØLines link attributes to entity sets and entity sets to relationship. ØEllipses represent attributes ØUnderline indicates identifier attributes (coming up) 22

Alternative E-R Notations 23

Example 1 • Build an ER Diagram for the following information: – Students • Have an Id, Name, Login, Age, Gpa – Courses • Have an Id, Name, Credit Hours – Students enroll in courses • Receive a grade 24

Example 1 Answer Name Id Login Students Age Id Gpa Courses Enrolled_In Grade 25 Name Credit

E-R Diagram Example Start Date Salary Number SIN manages 1 1 EMPLOYEE Birthday Name DEPARTMENT N works for Address 1 Name Location