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 should 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.
















































































































































































学霸联盟