08 - Logical Database Design

CSI 212 Database Management Systems 08-Logical Database Design: 

CSI 212 Database Management Systems 08-Logical Database Design Presenter: Girendra Persaud September 2011

Session Outline: 

Important Dates Review of the Design Phases Revisiting Holly’s ER Model Preparing Relational Schemas Table Mapping & Instance of Relationships Introduction to the Relational Data Model Conclusion Questions Resources Session Outline

Important Dates: 

Project Proposal Submission: Sept 22 nd 2011 @ 3:30PM Test #1: Oct 13 th 2011 Test #2: November 29 th 2011 Project Presentation & Submission: Dec 1 st 2011 Important Dates

Phases of Database Design: 

Phases of Database Design

Holly’s ER Model: 

Holly’s ER Model INVENTORY SUPPLIERS CUSTOMERS Clerks Supply Sold SID IID Name CLID Name Name CID Description Quantity Address Email Business Name Contact Note Address Email Telephone Telephone Note SOID DateTime IID CID CLID Total Re_Level DateTime Quantity Retail Price Price

Relational Schemas/Data Model Mapping: 

Process of mapping the fields and deciding on appropriate data types (specific to the DBMS of choice) From our Case Study: Holly’s Retail Centre Relations: Suppliers, Inventory, Clerks, Customers Relationships: Supply, Sold Relational Schemas/Data Model Mapping

Relational Data Schema for Holly’s : 

Suppliers( sid : INT(6), businessname : VARCHAR(120); contactperson : VARCHAR(60); note: TEXT, email: VARCHAR(40); address: VARCHAR(140); telephone: VARCHAR(15)) Inventory( iid:INT (6), name: VARCHAR(60); re_level : INT(4); quantity:INT (6), description: TEXT, retailprice : DECIMAL) Clerks () Customers() Supply() Sold() Relational Data Schema for Holly’s

Table Mapping Suppliers -> Supply -> Inventory : 

Suppliers Supply Inventory Table Mapping Suppliers -> Supply -> Inventory sid businessname telephone 1 A&L Supplies 555-5656 2 Tom’s Oil 555-7878 4 Electer Depot 432-2323 supid sid iid date quantity price 1 2 34 2011-03-12 48 10056.20 2 3 23 2011-03-23 96 10034.12 iid name quantity re_level retail_price description 34 Dvd players 70 6 15000 … 23 speakers 60 12 15000 …

Relational Data Model - Intro: 

What is? The Relational Data Model is a structure that describes data in the form of relations (tables). Terms: Degree: total columns Cardinality: total rows Relational Data Model - Intro

Conclusion: 

Logical Database Design is the process of mapping the fields (attributes) and deciding on appropriate data types (specific to the DBMS of choice) Selecting a data type is based on The DBMS specifications Smart use of space Suitability The Relational Data Model is a structure by which data in arranged in tables. Conclusion

Questions?: 

Questions?

Resources: 

http://www.edm2.com/0612/msql7.html http://www.readwriteweb.com/enterprise/2009/02/is-the-relational-database-doomed.php Resources