ORACLE microsystems Lecture 5 Entity-Relationship Model
Lecture 5 Entity-Relationship Model
1. Design Process 2.E-R Model 3.E-R diagram Outline 4.Advanced Topics 5. Reduction to relation schemas 6. Database design ZUML
Outline 1.Design Process 2.E-R Model 3.E-R Diagram 4.Advanced Topics 5.Reduction to Relation Schemas 6.Database Design 7.UML
Design Process
Design Process
Design Phases The initial phase of database design is to characterize fully the data needs of the prospective database users >Next, the designer chooses a data model and, by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database >a fully developed conceptual schema also indicates the functional requirements of the enterprise. In a specification of functional requirements, users describe the kinds of operations or transactions that will be performed on the data
Design Phases ➢The initial phase of database design is to characterize fully the data needs of the prospective database users. ➢Next, the designer chooses a data model and, by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database. ➢A fully developed conceptual schema also indicates the functional requirements of the enterprise. In a “specification of functional requirements”, users describe the kinds of operations (or transactions) that will be performed on the data
Design Phases(cont) The process of moving from an abstract data model to the implementation of the database proceeds in two final design phases. Logical Design- Deciding on the database schema. Database design requires that we find a good collection of relation schemas Business decision What attributes should we record in the database? Computer Science decision- What relation schemas should we have and how should the attributes be distributed among the various relation schemas? Physical Design- Deciding on the physical layout of the database
Design Phases(cont) ➢The process of moving from an abstract data model to the implementation of the database proceeds in two final design phases. • Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. • Business decision – What attributes should we record in the database? • Computer Science decision – What relation schemas should we have and how should the attributes be distributed among the various relation schemas? • Physical Design – Deciding on the physical layout of the database
Design Approaches Entity relationship Model(covered in this chapter Models an enterprise as a collection of entities and relationships Entity:a“ thing”or“ object” in the enterprise that is distinguishable from other objects Described by a set of attributes Relationship: an association among several entities Represented diagrammatically by an entity-relationship diagram Normalization Theory Formalize what designs are bad, and test for them
Design Approaches • Entity Relationship Model (covered in this chapter) • Models an enterprise as a collection of entities and relationships • Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects • Described by a set of attributes • Relationship: an association among several entities • Represented diagrammatically by an entity-relationship diagram: • Normalization Theory • Formalize what designs are bad, and test for them
E-R Model
E-R Model
ER model --Database Modeling >The eR data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database >The ER model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema Because of this usefulness, many database-design tools draw on concepts from the ER model >The eR data model employs three basic concepts entity sets ° relationship sets, attributes >The ER model also has an associated diagrammatic representation the eR diagram, which can express the overall logical structure of a database graphically
ER model -- Database Modeling ➢The ER data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database. ➢The ER model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Because of this usefulness, many database-design tools draw on concepts from the ER model. ➢The ER data model employs three basic concepts: • entity sets, • relationship sets, • attributes. ➢The ER model also has an associated diagrammatic representation, the ER diagram, which can express the overall logical structure of a database graphically
Entity Sets >An entity is an object that exists and is distinguishable from other objects Example: specific person, company, event, plant P An entity set is a set of entities of the same type that share the same properties Example: set of all persons, companies, trees, holidays >An entity is represented by a set of attributes; i.e descriptive properties possessed by all members of an entity set Example instructor (ID name street sala course=(course id, title, credits) >A subset of the attributes form a primary key of the entity set; i.e., uniquely identifying each member of the set
Entity Sets ➢An entity is an object that exists and is distinguishable from other objects. Example: specific person, company, event, plant ➢An entity set is a set of entities of the same type that share the same properties. Example: set of all persons, companies, trees, holidays ➢An entity is represented by a set of attributes; i.e., descriptive properties possessed by all members of an entity set. Example: instructor = (ID, name, street, city, salary ) course= (course_ id, title, credits) ➢A subset of the attributes form a primary key of the entity set; i.e., uniquely identifying each member of the set
Entity Sets --instructor and student instructor iD instructor name student -ID student name 76766 Crick 98988 Tanaka 45565 Katz 12345Shankar 10101 Srinivasan 00128 Zhang 98345Kim 76543 Brown 76543 Singh 76653Aoi 22222 Einstein 23121 Chavez instructor 44553 Peltier student
Entity Sets -- instructor and student instructor_ID instructor_name student-ID student_name