Entity-Relationship ModelSlide 2Database designIs relational model suitable for database designers?Different levels call for different data modelsSlide 6Conceptual modelingSlide 8Slide 9Slide 10ER model conceptsPurpose of E/R ModelEntity (entities)Entity Set & Entity typeAttributesTypes of attributesAtomic vs. CompositeSlide 18Single-valued vs. MultivaluedStored vs. Derived AttributesNesting composite and Multi-valued attributesNull valuesWhen NULL values will be used.E/R DiagramsExampleSlide 26Slide 27Relationship typesRelationship TypesRelationship SetSlide 31RelationshipsSlide 33Slide 34Slide 35Slide 36Slide 37Slide 38Multiway RelationshipsSlide 40Cardinality ratioOne-One RelationshipsMany-One RelationshipsSlide 44Many-Many RelationshipsIn PicturesSlide 47Slide 48Slide 49Slide 50Slide 51Slide 52Participation ConstraintsParticipationSlide 55Slide 56Slide 57Slide 58Slide 59Slide 60Slide 61Slide 62Slide 63Slide 64Slide 65Slide 66Weak Entity SetsSlide 68In E/R DiagramsWeak Entity-Set RulesWeak entity typesOne more example of weak entity typeAttributes on RelationshipsSlide 74Slide 75Roles (related to recursion)Slide 77Slide 78KeysKeys in E/R DiagramsExample: name is Key for BeersExample: a Multi-attribute KeyremarksExample: GoodExample: BadSlide 86Entity, Relationship or attributesVarious constraintsDesign TechniquesAvoiding Redundancy1Entity-Relationship ModelER-DiagramsWeak entity sets2Outline: Conceptual modeling using ER-model•Data modeling •Entity-relationship model- Entity types- strong entities (regular)- weak entities- Relationships among entities- Attributes - attribute classification- Constraints- degree- cardinality constraints- participation constraints - …3Database designDatabase design is an important and challenging task.The relational model is the model used by RDMBS.Is the relational model the best model used by businessmen to describe their business (logics)?4Is relational model suitable for database designers?While a simple data model that is consisting of only a few tables can be created "manually“, a large application needs a more systematic approach. While the relational data model of the RDBMS still hides many details, it is nonetheless closer to how the RDBMS stores data than to how a user thinks about the underlying enterprise.Different levels call for different data modelsGood design requires a thorough understanding the data associated with an enterprise and how they are used, as well as a solid understanding of the functionalities of your DBMS (Oracle in our case)At least two levels understanding here!•Application•implementation56Conceptual Semantic data models are needed to bridge the gap and assist the database design process.Being syntactically correct does not mean being semantically correct.7Conceptual modelingA high-level conceptual model provides concepts for describing the database structure that are close to the way users perceive data.A conceptual schema describes the structure of the database by hiding the details of physical storage structures.Conceptual modeling is the process of creating a conceptual schema using a high-level conceptual model.8Mini WorldRequirements Collection and AnalysisConceptual DesignLogical Design (Data Model Mapping)Physical DesignDatabase RequirementsConceptual Schema (high level)The main phases of database application DesignFunctional AnalysisFunctional AnalysisDBMS-independentDBMS-specificAPPLICATION PROGRAM DESIGNTRANSACTION IMPLEMENTATIONLogical Schema (in the data model of a sepcific DBMS)Internal SchemaApplication programsHigh level transaction specification9ExampleThe company database keeps track of a company’s employees, departments, and projects:Requirements:concerning the department:1. The company is organized into departments2. Each department has a unique name, a unique number, and a specific employee is its’ manager3. we track the start date for the manager function4. a department may be in several locations5. a department controls a number of projectsconcerning the project:6. a project has a unique name, a unique number, and is in a single location10example continuedconcerning the employee:7. each employee has a name, social insurance number, address, salary, sex, and birth date8. an employee is assigned to one department but may work on several projects which are not necessarily controlled by the same department9. we track the number of hours per week that an employee works on each project10. we keep track of the direct supervisor of each employee11. we track the dependents of each employee (for insurance purposes)concerning the dependent:12. we record each dependent’s first name, sex, birth date, and relationship to the employee11ER model conceptsThe Entity-Relationship (ER) model is a popular high-level conceptual model which helps describe a real application scenario in a pictorial format. It employees the notions of entities, attributes and relationships, as well as constraints.This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts.It was introduced by P. Chen (bit.csc.lsu.edu/~chen) in "The Entity-Relationship model - Toward a Unified Views of Data", TODS 1:1, March 1976.12Purpose of E/R ModelThe E/R model allows us to sketch the design of a database informally.Designs are pictures called entity-relationship diagrams.Fairly mechanical ways to convert E/R diagrams to real implementations like relational databases exist.13Entity (entities)Entity = “thing” or object.An entity is a specific object or thing in the mini-world (for instance, a company) with an independent existence.Example:the EMPLOYEE entity David, the research DEPARTMENT, the product X PROJECT14Entity Set & Entity type Entity type defines the data type of a collection of entities having the same attributes (that are of the same type).Similar to a class in object-oriented languages.Entity set = collection of similar entities of a particular entity type in the database at any point in time.The same name is used for both the entity type and its entity set. They are interchangeably used when the context is clear.15Attributes Attribute = property of an entity set.Generally, all entities in a set have the same properties.Attributes are simple values, e.g. integers or character strings.Attributes are properties used to