CMPT 354: Database Systems 1 – Unit 02 – Database Models & Design Dr. Jack Thomas Simon Fraser University Summer 2021 The Entity Relationship Model Unit Database Design The ER Model – Entity Sets – Relationship Sets – Weak Entity Sets – Subclasses – Aggregation ER Design Issues Which Database Model Until recently, the relational model was the prevalent model for databases. There have been two main challenges to this: 1. Object oriented programming Object models do not match well to relational models. 2. The rise of the web and Big Data Large amounts of distributed data Difficult to manage in a conventional relational database. SQL or NoSQL The data model of an SQL database is derived from set theory. CMPT 354’s focus is on relational databases. – Assumes that an enterprise is to be modeled as a relational database. NoSQL databases have different data models. – With variable schema. – Discussed later in the course. Goal of Database Design Produce a set of Tables that can store the data required by an Enterprise. Relational database tables represent Entities and Relationships between entities. A database of students might include the entities student and course, and the relationship student takes course. Database Design Database applications often model large and complex enterprises. – It’s common that no one person will understand the complete requirements. Database designers must discuss requirements with users. – Translating requirements into a high-level design. – Confirm that design with the users. – Translate the high-level design into a database schema. Design Overview Requirements Analysis Conceptual Database Design Logical Database Design – Schema Refinement Physical Database Design Security Design Requirements Analysis A database is intended to model a real-world enterprise. – What data is to be stored – What applications are required to work with the database – Which are the most frequent, and most important operations Conceptual Database Design Use a model to develop a high-level description. – This course focuses on the Entity-Relationship model. Identify the entities and relationships. – And information to be stored about them. Identify integrity constraints. – Also known as business rules. Check with the client that the model is appropriate. – Correct the model and repeat as necessary. Logical Database Design Determine which data model should be used to implement the database. Determine which DBMS to use. – This typically means deciding which existing DBMS product to purchase or license. Map, or translate, the conceptual schema to a database schema of the chosen model. – We will specify the conceptual schema by drawing an Entity Relationship diagram. Design Notes The designer must decide how to represent the stuff to be modeled in the database. – Things to be represented in a database are entries. – Connections between things are relationships. Customer buys Products There are two major problems to be avoided: 1. Redundancy – information sho
uld not be repeated. 2. Incompleteness – it should be possible to record all desired information. The Entity-Relationship Model The ER model has similarities to other modeling languages like UML. The major components of the ER Model are: – Entities, such as people, cars, accounts, things… – Attributes that describe the entities, e.g. name, age, amount, date… – Relationships that connect the entities, e.g. customer owns account, student takes course. – Constraints which restrict relationships, e.g. an account must be owned by a customer. Entity-Relationship Diagrams Entity-Relationship diagrams show the structure of a database graphically. – Simple symbols: rectangles, diamonds, ovals and lines represent the components of the ER model. – They are straightforward and easy to explain to users. There are many variations of ER diagrams. – Don’t expect the symbols in every ER diagram you see to be the same! – Some common variations are discussed at the end of these units on Entity-Relationships. DATABASE DESIGN REVIEW May 20th Addendum Database Design – The Big Picture To take things from the top, we should imagine ourselves as would-be database developers looking to build a database for a large Enterprise which needs to organize and store its Data. The Overall Process of Design Designing a database covers the work from discovering what the needs (or requirements) of the enterprise are all the way to setting up the different user accounts on the finished system. The phases can be divided as follows: – Requirements Analysis (The needs) – Conceptual Database Design (The model) – Logical Database Design (The diagram) Schema Refinement (The table) – Physical Database Design (The hardware) – Security Design (The accounts and access) Our attention will mostly be on the Conceptual and Logical phases, drawing up plans for the database and then implementing them. The Entity-Relationship Units While there are different models you can use to describe the data of an enterprise, the Relational model has been traditional, and will be our focus. This requires describing data in terms of Entities and the Relationships between them, and turning them into Entity-Relationship diagrams which we can later translate to database tables using SQL. A Picture is Worth a Thousand Words Here is a preview of an Entity-Relationship diagram borrowed from a future unit. The shapes describe different elements – rectangles for Entities, diamonds for Relationships, ovals for Attributes. We’ll be explaining and expanding your library of ER diagram tools during these coming units. Recap – Data Basics The goal of database design is to produce a set of Tables that store the data of an Enterprise. The Relational Model is a popular way to organize this data into Entities and the Relationships between them. The design process is split into phases, including Requirements Analysis, Conceptual Design, Logical Design, Physical Database Design, and Security Design. Entity Relationship Diagrams are visualizations of a system we will eventually translate into our database.