ICT394 Business Intelligence Application DevelopmentDr Danny TooheyICT285 DatabasesDr Danny TooheyTopic 05: Conceptual database design
About this topicThis is the first of three topics that deal specifically with database design as a process. Database design can be divided into three basic stages: conceptual, logical and physical. The conceptual designphase (this topic) constructs a model of the enterprise’s data needs independent of any database model. This ensures firstly that design can progress without the need for compromise due to limitations in the data model, and secondly that the data model most appropriate to the particular enterprise’s requirements can be selected when the requirements are more fully known.
Topic Learning OutcomesAfter completing this topic you should be able to:•Explain where conceptual data modelling fits in the database development life cycle and SDLC•Describe the features of the Entity-Relationship model: entities, attributes, relationships, cardinality, weak entities•Describe the features of the Extended Entity-Relationship model: supertypes and subtypes•Interpret different types of Entity-Relationship diagram notation•Draw an Entity-Relationship Diagram from a description•Use Entity-Relationship modelling as a means of eliciting and checking user requirements for a system•Check your Entity-Relationship Diagram for correctness•Avoid typical ‘traps’ of poor ER modelling practice
Resources for this topicREADING•Text, Chapter 5 “Data Modelling with the Entity-Relationship Model"•Chen, P.P.S., 1976, The entity-relationship model-toward a unified view of data, ACM Transactions on Database Systems, 1(1), pp. 3-36. On My Unit Readings(a classic paper, but not examinable)Kroenke, D.M., and Auer, D.J., 2016, Database Processing: Fundamentals, Design and Implementation, 14th Edition, Pearson, Boston.
Lab 05In Lab 05 you will practice creating several ERDs that demonstrate some of the typical patterns found in data modelling. We will use Microsoft Visio for this. Visio is a drawing tool that can be used to create many different types of diagrams, including ERDs. It is a useful tool for ER modelling as it not only draws the diagram, but allows you to create properties and constraints that apply to it. It therefore helps move us from conceptual design (the ERD) towards logical design (a set of normalised tables).
Topic Outline1.Where conceptual modelling fits into database design2.Entity-relationship modelling•Entities and attributes•Relationships•Weak entities and ID-dependent entities•Generalisation/specialisation•Different notations3.How to construct an ERD4.How to check your ERD for correctness (separate document)
Topic 05: Part 01Where conceptual modelling fits in database design
Database DesignProcess of creating a design for a database that will support the enterprise’s mission statement and mission objectives for the required database systemThree phases of database design:-Conceptual database design-Logical database design-Physical database design