250 likes | 259 Views
Learn about conceptual database design, requirements modeling, schema design, and implementation to build an application using a DBMS. Understand the importance of database design and formalisms like ODL and E/R model. Also, explore multi-way relationships and learn how to convert them to binary relationships. Discover design principles for effective database design.
E N D
Building an Application with a DBMS • Requirements modeling (conceptual) • Decide what entities should be part of the application and how they should be linked. • Schema design and implementation • Decide on a set of tables, attributes. • Define the tables in the database system. • Populate database (insert tuples). • Write application programs using the DBMS • Way easier now that the data management is taken care of.
Database Design • Why do we need it? • Agree on structure of the database before deciding on a particular implementation. • Consider issues such as: • What entities to model • How entities are related • What constraints exist in the domain • How to achieve good designs
Database Design Formalisms 1. Object Definition Language (ODL): • Closer in spirit to object-oriented models • Not in the scope of this course 2. Entity/Relationship model (E/R): • More relational in nature. • Related to the EER modeling in the MySQL workbench but we use different conventions here! • Both can be translated to relational schemas
Entity / Relationship Diagrams Entity sets Attributes Relationships between entities Product address buys
Keys in E/R Diagrams • Every entity set must have a key name category price Product
name category stockprice name price makes Company Product buys employs Person address name ssn
makes Company Product 1 a b 2 A= =B c 3 d What is a Relation ? • A mathematical definition: • if A, B are sets, then a relation R is a subset of A B • A={1,2,3}, B={a,b,c,d}, R = {1,a, 1,c, 3,b} - makes is a subset of ProductCompany:
1 2 3 1 2 3 1 2 3 a b c d a b c d a b c d Multiplicity of E/R Relations • one-one: • many-one • many-many
name category stockprice name price makes Company Product What doesit mean? buys employs Person address name ssn
Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Product Store Purchase Person Can still model as a mathematical set (how?)
Role Movie Plays Salary Actor Arrows in Multiway Relationships Q: what does the arrow mean ? A: if I know the movie, role and actor, I know the salary too
Arrows in Multiway Relationships Q: what do these arrow mean ? A: store, person, invoice determines movie and store, invoice, movie determines person Invoice VideoStore Rental Movie Person
Invoice VideoStore Rental Movie Person Arrows in Multiway Relationships Q: how do I say: “invoice determines store” ? A: no good way; best approximation: Q: Why is this incomplete ?
Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase Store buyer seller Person
Attributes on Relationships date Product Purchase Store Person
Converting Multi-way Relationships to Binary ProductOf date Product StoreOf Purchase Store BuyerOf Person
From E/R Diagramsto Relational Schema • Entity relation • Relationship relation
Entity Set to Relation name category price Product Product(name,category, price) name category price gizmo gadgets $19.99
Relationships to Relations price name category Start Year name makes Company Product Stock price Makes(product-name,product-category,company-name, year)Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963 (watch out for attribute name conflicts)
Relationships to Relations price name category Start Year name makes Company Product Stock price No need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks
Multi-way Relationships to Relations name address Product Purchase Store name price Person Purchase( , , ) ssn name
3. Design Principles What’s wrong? Purchase Product Person President Country Person
Design Principles:What’s Wrong? date Product Purchase Store Moral: pick the right kind of entities. personName personAddr
Design Principles:What’s Wrong? date Dates Product Purchase Store Moral: don’t complicate life more than it already is. Person