chap3lecture

Data Modeling: 

Data Modeling Database is self describing collection of integrated records Databases vary in their level of detail (simple or crude) Database is a dynamic model Building a data model is a process of inference Quality of the model is important

Data Modeling: 

Data Modeling Creating a model of the users’ data , the most important task in database development It involves interviewing,requirement specification,building the data model and prototypes. Data Modeling -Process of creating a logical representation of the structure of the database

Entity-Relationship Model: 

Entity-Relationship Model E-R Model Peter Chen, 1976

Entities: 

Entities “something that users track” “a set of objects that are distinguished” Page 49 © Prentice Hall

Attributes (properties): 

Attributes (properties) “describe the entity’s characteristics” Entity: Employee Attributes: Employee-Name, Extension, Date-Of-Hire, Job-Skill-Code

Identifier : 

Identifier “attributes that uniquely identify entity instances” Entity: Employee Identifier: Social-Security-Number

Relationships: 

Relationships “associations between entities” Page 51 © Prentice Hall

A Relationship: 

A Relationship can have attributes but NOT an identifier” Page 51 © Prentice Hall

Cardinality: 

Cardinality “maximum or minimum number of entities that can occur on one side of a relationship” Page 52 © Prentice Hall

Minimum Cardinality: 

Minimum Cardinality HASH an entity must exist in the relationship Minimum Cardinality = 1 A Dormitory must be occupied by at least one Student OVAL there may or may not be an entity in the relationship Minimum Cardinality = 0 A Student is not required to occupy a Dormitory Page 52 © Prentice Hall

Maximum Cardinality: 

Maximum Cardinality 1 means Maximum Cardinality = 1 A Student can occupy a maximum of 1 Dormitory N or M means Maximum Cardinality = Many A Dormitory can be occupied by a maximum of many Students Page 52 © Prentice Hall

Recursive relationships: 

Recursive relationships “relationships among entities of a single class” Page 53 © Prentice Hall

E-R Diagram: 

E-R Diagram Page 54 © Prentice Hall

E-R Diagram: 

E-R Diagram Page 54 © Prentice Hall

Weak Entities: 

Weak Entities “an entity whose presence in the database depends (logically) on another entity” On the ER diagram, make the entity and the relationship with ROUNDED CORNERS (textbook) Or DOUBLE the LINES

Weak Entities: 

Weak Entities Employee Dependent Has 1:N Emp# Name

Weak Entities & Relationships: 

Weak Entities & Relationships EMPLOYEE has DEPENDENTS BUILDING has APPARTMENTS PATIENT has PRESCRIPTIONS How is that different from: STUDENT has ADVISORS SALESPERSON has ORDERS CUSTOMER has ACCOUNT

Subtype Entities: 

Subtype Entities “an entity that contains option sets of attributes” Page 56 © Prentice Hall

Multivalued Attribute: 

Multivalued Attribute “an attribute that can have multiple values simulatenously” On the ER diagram, make the attribute with double circles

Multivalued Attribute: 

Multivalued Attribute Employee Emp# Phone#

Complete E-R Diagram: 

Complete E-R Diagram Page 59 © Prentice Hall

Drawing E-R diagrams: 

Drawing E-R diagrams Make a list of all entities and their attributes. Identify Weak entities Make a list of the relationships to be presented and any attributes for them Draw the ER diagram Identify cardinalities and identifiers

Advises on Drawing E-R diagrams: 

Advises on Drawing E-R diagrams The task make take several attempts, and different designers arrive at different solutions. Use abstractions to group attributes into entities. If several attributes tend to appear together, then they may be attributes of the same entity. Avoid the temptation of making all reports and/or transactions into entities. Remember what differentiates an entity from a relationship when they are transformed to tables (LATER).

Drawing E-R diagrams: 

Drawing E-R diagrams IEW IEF DEFT ER-WIN Visio

Slide25: 

Example : Jefferson Dance club The Jefferson Dance Club teaches social dancing and offers both private and group lessons.Jefferson charges $45 per hour per student for a private lesson and $6 per hour per student for group lesson.Jefferson employs two types of instructor: full time and part time.In addition to the lessons, Jefferson sponsers two weekly social dances featuring recorded music.The admission charge is $5 per person.The Friday night dance is more popular and averages around 80 people.The purpose of the dances is to give the students a place in which to practice their skills. Jefferson would like to develop an information system to keep track of students and the classes they have taken.Jefferson’s managers would also like to know how many and which types of lessons each teacher has taught and to be able to compute the average cost per lesson for each of their instructors.

Jefferson Dance Club: 

Jefferson Dance Club Page 62 © Prentice Hall

Draw E-R diagram: Example: 

Draw E-R diagram: Example ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost. Also the organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one project and one equipment item. Any given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.

Entities???: 

Entities??? ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost. Also the organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one project and one equipment item. Any given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.

Entities’ Attributes???: 

Entities’ Attributes??? ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost. Also the organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one project and one equipment item. Any given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.

Entities & Attributes: 

Entities & Attributes Chemist Emp# Phone# Project Proj# Start-Date Equipment Serial# Cost

Relationships & their Attributes???: 

Relationships & their Attributes??? ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost. Also the organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one project and one equipment item. Any given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.

Entities/Relationships& their Attributes: 

Entities/Relationships & their Attributes Chemist Emp# Phone# Project Proj# Start-Date Equipment Serial# Cost Works-On Uses Date-Assigned Assign-Date

Cardinalities?????: 

Cardinalities????? ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost. Also the organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one project and one equipment item. Any given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item.

Complete ER Diagram: 

Complete ER Diagram Chemist Emp# Phone# Project Proj# Start-Date Equipment Serial# Cost Works-On 1:N Uses 1:N Date-Assigned Assign-Date

San Juan Charters: 

San Juan Charters Page 66 © Prentice Hall