Database Notes

Database Notes - Relevant Textbook Chapters Textbook...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Relevant Textbook Chapters Textbook Chapters Chapter Information Technology Example: Databases MIS Division Sauder School of Business University of British Columbia Vancouver, British Columbia, Canada 7.1 and Plug-In T5. Regarding Plug-In T5 The textbook you purchased should come textbook you purchased should come with a password that allows you to download Plug-In chapters from the publisher’s web site. Credits: Professor Jai-Yeol Son COMM 391, Sauder School, UBC 2 Agenda Motivation Database concepts Entity-relationship diagrams diagrams Part I Motivation What are Databases? What are Databases? Databases hold data Databases can help you search for data Databases can tell you information about data Databases can link pieces of data together How Many Used a Database? Many Used Database? ALL OF YOU! OF YOU! How many of you have used a many of you have used database? COMM 391, Sauder School, UBC 5 COMM 391, Sauder School, UBC 6 Databases - Itunes Itunes Itunes Itunes Business Database Systems Database Systems Businesses holds data about your music can help you search your music need data to run their operations data data Itunes can tell you information about your music data can link pieces of music data What are the sales from June? When will people come into our stores? Who are our employees? How much should they be paid? Itunes COMM 391, Sauder School, UBC 7 COMM 391, Sauder School, UBC 8 So What? So What? What So, What is a Database? What is Database? A Database Management Systems (DBMS) is An organized collection of logically related organized collection of logically related data. Uses special database management special database management software to Reduce data redundancy, data redundancy Share data in a controlled way, and Reduce data integrity problems. data integrity problems COMM 391, Sauder School, UBC 10 is so big deal about putting data into a database? How can you ensure that can you ensure that There is no data redundancy? Data can be shared? can be shared? Also, how can data be shared in a way that it does not violate privacy and confidentiality? There is data consistency (or data integrity)? Can you think of examples that the above can happen? COMM 391, Sauder School, UBC 9 Storing Data in Tables Data in Tables Compose Division MIS Representing Data in Tables Data in Tables of tables (rows and columns) Assistant Paula Chung Office Phone HA 662 604-822-0490 HA 664 604-822-8314 How about these two tables? Division MIS Assistant Paula Chung Office Phone HA 662 604-822-0490 HA 664 604-822-8314 Office HA 668 HA 093 Phone 604-822-8390 604-827-5285 Marketing Florence Yen … Where Marketing Florence Yen … Division MIS MIS … 11 are you going to store instructors (name, office, phone) for each division? Add columns to the above table? Instructor Carson Woo Kafui Monu How many columns to add? Make a new table? COMM 391, Sauder School, UBC Who is Carson Woo’s divisional assistant? COMM 391, Sauder School, UBC 12 User’s Role Role DBMS can only enforce domain independent issues. Users will have to deal with application will have to deal with application specific requirements. Data evolution: evolution: Often, users start to keep track of a small amount of data. small amount of data If the way to keep track of data is not properly done, then lot of time will be wasted when done, then a lot of time will be wasted when the amount of data grows. COMM 391, Sauder School, UBC 13 Part II. Database Concepts Concepts Relational Databases: Table Relational Databases: Table • • • • • • Data organized as two-dimensional tables. Relation: table table Record (= Tuple) : row in table Field (= attribute) : column in table Primary key : field whose value uniquely identifies each row Each cell can contain only one value Employee Table Relational Databases: Table Relational Databases: Table Each table must have a primary key that uniquely identifies each row. Composite primary key a primary key that consist of more than one field Atomic primary key a primary key that consist of only one field primary key that consist of only one field ID 10 11 11 12 13 14 14 LNAME ABIN BAXTER CHEN DENVER CHEN FNAME SMITH ALEX KOO LEWIS ALEX HOURS RATE 40.00 $ 10.35 38.00 $ 9.50 40.00 $ 9.25 38.00 $ 9.50 38.00 $ 9.50 15 Can you give me some examples? COMM 391, Sauder School, UBC COMM 391, Sauder School, UBC 16 Composite Primary Key Composite Primary Key Building_No 1 1 1 2 2 2 3 3 Room _NO 100 101 200 100 101 102 200 201 Room _Type A A B C C A A C # of Bedroom 4 3 3 2 2 1 2 2 Furnished y y y n n n n y Relational Databases: Linking Tables Databases: Linking Tables Collection of logically linked Tables Tables are linked by matching fields Primary Key and foreign Key Primary Key and Foreign Key are used to create logical relationships between tables Dormitory (Housing Unit) Table Primary Key is an attribute that uniquely identities each record in a table Foreign Key is an attribute in a table that reference th a primary key of another table. COMM 391, Sauder School, UBC 17 COMM 391, Sauder School, UBC 18 Example: Small-Town Book Store Small Book Store Customer cus-num name 100 Poulos 114 Simmons 115 Thompson 118 Simmons Purchase Pur_num 1000 1001 1002 1003 1004 1005 Book book-code GW4-5 BS7-8 PQ1-2 PQ1-3 title Blazing Sun Bright Star Cattle Run Cattle Run Bright Star Example: Trucking Company Trucking Company TRUCK TRUCK_NUM 1001 1002 1003 1004 1005 1006 BASE_CODE 506 502 501 505 503 501 507 502 503 TYPE_CODE 1 1 2 1 2 2 3 3 2 TRUCK_MILES 32123 76984 12346 894 45673 93245 32012 74213 32015 TRUCK_BUY_DATE 11/8/94 3/23/92 12/27/95 2/21/96 4/15/94 3/23/92 12/1/94 11/8/94 4/15/94 cus-num book-code date 114 BS7-8 9/16/02 100 GW4-5 6/17/03 114 PQ1-3 9/17/04 114 GW4-5 7/17/04 115 PQ1-2 9/17/04 100 BS7-8 9/18/04 COMM 391, Sauder School, UBC 1007 1008 primary keys? keys? foreign keys? 1009 19 COMM 391, Sauder School, UBC 20 Example: Trucking Company (cont (cont’d) BASE BASE_CODE 501 502 503 504 505 506 BASE_CITY Murfreesboro Columbus Hampton Columbus Cleveland Dalton BASE_STATE TN OH MO GA IL GA AREA_CODE 615 614 456 770 614 770 BASE_PHONE 523-4567 293-5678 345-6789 233-3843 523-4567 456-7890 BASE_MGR Andrea Gallager John Smith Maria Talindo John Smith Peter McAvee John Smith Integrity Constraints Constraints Entity TYPE TYPE_CODE 1 2 3 DESCRIPTION_1 Single box Single box Tandem trailer DESCRIPTION_2 double-axle single-axle single-axle Table TRUCK BASE TYPE Primary Key ________ ________ ________ Foreign Key ________ ________ ________ Integrity: (1) unique value (2) no null value in primary key field Referential Integrity: A foreign key may Integrity foreign key may have either (1) a null value, or (2) a value that matches value in the primary key of that matches a value in the primary key of a linked table COMM 391, Sauder School, UBC 21 COMM 391, Sauder School, UBC 22 Violations of Entity Constraints of Entity Constraints • Department Table D_ID 1 2 3 4 NAME LOCATION MKTG B200 FINANCE A308 ACCTG B332 MIS A322 PROD A432 Violations of Referential Constraints of Referential Constraints • Two logically linked tables: Department and Employee D_ID 1 2 3 4 NAME LOCATION MKTG B200 FINANCE A308 ACCTG B332 MIS A322 Violation: Y or N If yes, why? Violation: Y or N If yes, why? D_ID 1 2 3 2 4 NAME LOCATION MKTG B200 FINANCE A308 ACCTG B332 MIS A322 PROD A432 COMM 391, Sauder School, UBC Violation: Y or N or If yes, why? E_ID 10 11 12 13 23 LNAME ABIN BAXTER CHEN DENVER FNAME SMITH ALEX KOO LEWIS HOURS 40.00 38.00 40.00 38.00 $ $ $ $ RATE 10.35 9.50 9.25 9.50 DEPT 3 4 2 24 COMM 391, Sauder School, UBC Violations of Referential Constraints Violations of Referential Constraints • Two logically linked tables: Department and Employee D_ID 1 2 3 4 NAME LOCATION MKTG B200 FINANCE A308 ACCTG B332 MIS A322 Music Database Database Can you think of other tables that you would have in music database? in a music database? Violation: Y or N If yes, why? E_ID 10 11 12 13 LNAME ABIN BAXTER CHEN DENVER FNAME SMITH ALEX KOO LEWIS HOURS RATE 40.00 $ 10.35 38.00 $ 9.50 40.00 $ 9.25 38.00 $ 9.50 DEPT 3 4 2 7 25 COMM 391, Sauder School, UBC 26 COMM 391, Sauder School, UBC Agenda Part III Database Design Design Steps in DB Development in DB Development Conceptual Model (Drawing an E-R) Logical Design (Converting an E-R Design (Converting an model to Tables) Examples COMM 391, Sauder School, UBC 28 Building the Music Database Building the Music Database Consider all the data you need to keep track in music database in a music database? How can you systematically identify the tables and primary and foreign keys? and primary and foreign keys? Steps in the DB Development Process in the DB Development Process User view 1 view User view 2 Conceptual Model User view 3 Conceptual Modeling Modeling • ER Modeling • OO Modeling Logical Modeling • Hierarchical DB • Network DB DB • Relational DB • OO DB Physical DB design Physical DB Table 1 Table 2 Table 3 Table n (In case of relational DB) case of relational DB COMM 391, Sauder School, UBC 29 COMM 391, Sauder School, UBC 30 Steps in the DB Development Process in the DB Development Process 1. Integrating different views about “Students” in conceptual DB modeling “Students” in conceptual DB modeling Conceptual Data Modeling Define high-level data types (entities) by integrating different views of end users, define major relationships among the entities, and add attributes on the entities. Analyze the overall data requirements and develop a blueprint to the overall data requirements and develop blueprint to be used in subsequent stages of DB development process E-R (Entity Relationship) Diagram What do they collect about students? entity 2. Logical Database Design Database Design Relational, Hierarchical, and Network DB Design Relational DB: Converting E-R Diagram to Tables Registration office: Stu_id, name, address, email St address, gender, BOD, phone number, transfer (y/n), year, high school graduated, … attribute Parking office: Stu_id, name, address, phone number, … Housing office: Stu_id, name, address, phone number, emergency contact name, emergency contact phone, emergency contact name, emergency contact phone, … Registration office: Students take classes. Students re major in ABC (e.g., Commerce). ... lationship Parking office: Students hold a parking permit. … Housing office: Students live in a dormitory room. ... office: Students live in dormitory room COMM 391, Sauder School, UBC 32 What activities of students do they keep track of? 3. 4. 5. Physical Database Design and Creation Database Implementation Database Maintenance Maintenance COMM 391, Sauder School, UBC 31 Conceptual Modeling: Constructing Constructing E-R Diagram Diagram Entities, Attributes, and Relationships Relationships Prof_id Professor Name E-R Diagram: a conceptual blueprint of a co database Graphical representation of all entity representation of all entity relationships Legend = Entity = Attribute Entities – Boxes (Rectangles) Relationships – Lines and Diamonds Attributes – Oval, or listed inside the entity Cardinalities teaches Time Class_id Room taken_by = Relationship Stu# Name Class Student COMM 391, Sauder School, UBC 33 COMM 391, Sauder School, UBC 34 Entity An entity (=entity class, entity set) is a person, place, event, or thing for which we intend to thi collect data. An entity is represented by a rectangle containing the entity’s name. Entity instance: occurrence of an entity class instance: occurrence of an entity class Examples: University -- Students, Professors, Courses, Classes, Departments Attribute Property or characteristic of an entity type Represented by an oval containing the attribute’s th name connected to the entity with a line. Primary keys are underlined. keys are underlined They are sometimes not shown in E-R diagrams, but listed in separate tables. sepa tab Examples: Student -- Student Number Name, GPA, Data , of Birth, Phone No COMM 391, Sauder School, UBC 35 COMM 391, Sauder School, UBC 36 Relationship Relationship A Simple Example Simple Example Prof_id Professor Name relationship is an association between/among entities. Represented by diamond-shaped symbols. by diamond symbols. Usually described by a verb Three basis types 1:1, 1:M, M:N The number of instances of one entity that can or must be associated with each instance of another entity another entity. COMM 391, Sauder School, UBC 37 1 teaches Time Class_id Stu# Name M Class Room N taken_by M Student COMM 391, Sauder School, UBC 38 Example 1: Fun Place Parent Directory Place Parent Directory Information to Look for? to Look for? Entities Attributes The director of the “Fun Place” preschool wants to keep track of the contact information of the doctors (M.D.) for children (in case she needs to contact children (in case she needs to contact them during an emergency). She wants to keep track of the ID (a unique no assigned to each doctor), the name, and telephone number, of the doctors and some basic information about the child (student no, name, and age). (Each student will have only one doctor. However, a doctor may have more than one child in the Preschool) have more than one child in the Preschool) COMM 391, Sauder School, UBC 39 Decide which is the primary key Relationships Cardinality (number of instances) COMM 391, Sauder School, UBC 40 Example 1: Fun Place Parent Directory Place Parent Directory Let’s See … See ID Name Tel. Doctor 1 The director of the “Fun Place” preschool wants to keep track of the contact information of the doctors (M.D.) for children (in case she needs to contact children (in case she needs to contact them during an emergency). She wants to keep track of the ID (a unique no assigned to each doctor), the name, and telephone number, of the doctors and some basic information about the child (student no, name, and age). (Each student will have only one doctor. However, a doctor may have more than one child in the Preschool) have more than one child in the Preschool) M COMM 391, Sauder School, UBC 41 1 treats Std.# M Child Name Age COMM 391, Sauder School, UBC 42 Composite Entity Entity For the purpose of this course: Created for M to N relationships Making a Composite Entity ! Degree of a Relationship of Relationship Put a rectangle over the diamond symbol Derive a primary key from associated entities or assign its own primary key A M to N binary relationship is converted into 2 oneto-many binary relationships. binary relationships You will learn about the more precise use of composite entity in Comm 437 composite entity in Comm 437 COMM 391, Sauder School, UBC 43 A relationship’s degree indicates the number of associated entities or participants. A unary relationship exists when an association is maintained within a single entity. (= Recursive relationship) --- an entity is associated with itself is associated with itself. A binary relationship exists when two entities are associated (Most common) entities are associated. (Most common) A ternary relationship exists when three entities are associated entities are associated. COMM 391, Sauder School, UBC 44 Logical Design: Converting ER to Tables ER to Tables 1. Examples: 1 to 1 Binary Relationship to Binary Relationship E_ID E_Name For each 1:1 binary relationship, create a relation for each 1:1 binary create relation for each of the two entity types, then include the primary key of one entity as a foreign key in the relation converted from the other entity from the other entity. For each 1:M binary relationship, create a relation for each of the two entity types, then include the primary key of the entity on 1-side as a foreign key in the relation converted from M-side entity. For each M:N binary relationship, make sure that you each M:N binary make sure that you created a composite entity and that you included primary keys of the two associated entities as attributes of the composite entity composite entity. COMM 391, Sauder School, UBC 45 Employee Address 1 Data Tables 2. manages Employee (E_ID, Address, E_Name) Department (Dept ID, Dept_Name, Location, Manager Manager_ID) 1 Dept_ID 3. Department Dept_Name Location Note: Manager_ID is the foreign key of E_ID from the Employee table. 46 46 COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC Examples: 1 to M Binary Relationship to Binary Relationship E_ID E_Name Examples: M to N Binary Relationship to Binary Relationship E_ID E_Name E_ID E_Name Employee Address M Data Tables Address Employee Address M Employee 1 M E_ID works_for Employee (E_ID, Address, E_Name, Dept Dept_ID) Department (Dept ID, Dept Dept_Name, Location) Location) works_on Start_Date N Start_Date Assignment N Proj_ID 1 Dept_ID 1 Proj_ID Department Project Proj_ID Project Dept_Name Location COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC 47 47 Proj_Name Location COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC Proj_Name Location 48 48 Examples: M to N Binary Relationship to Binary Relationship E_ID E_Name Draw an ER diagram for Access Tutorial an ER diagram for Access Tutorial You run a small business that sells computer parts to customers in North America. You list your products on your store Web site, and take orders over the Internet (on-line forms and emails) and Phone calls As your business grows you decided to design database and to emails) and Phone calls. As your business grows, you decided to design a database and to implement it using MS Access. In particular, you want to keep track of the information on customers, orders, products, and suppliers in the database. Data Tables Tables Employee Address 1 M Assignment Start_Date N 1 Proj_ID Proj_ID E_ID Employee (E_ID, Address Address, E_Name) Project (Proj_ID, Proj_Name, Location) Assignment (E_ID, Proj_ID, Start_Date) Project Proj_Name Location COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC 49 49 For customers, the database is to hold data about their ID (Customer ID, primary key), last name (Customer Lname), first name (Customer Fname), Card No, Card Type, Expiration, Address, City, State, and Zip code. For orders, your database is to hold data about the ID (Order ID, primary key), the date (Order Date), shipping company name (Ship Name), and the address to be shipped (Ship Address) Address). For products (e.g, RAM memories, Monitors, …), your database is to hold data about their ID (Product ID, primary key), name (Product Name), and Units in Stock. For suppliers, your database is to hold data about their ID (Supplier ID, primary key), Company Name, and Contact Name. A customer can make more than one order, but each order should be made by one and only one customer. Each order may include more than one product, and a particular product may be included in more than one order. In addition, when a product is included in an order, the database will hold data about the Unit Price Quantity and Discount hold data about the Unit Price, Quantity, and Discount. A supplier may supply us more than one product type, however, for a particular kind of product, we purchase it from one and only one supplier. COMM 391, Sauder School, UBC 50 1 Provide M 1 Provide M M 1 Unit price M Quantity Discount Is_included M N 1 1 1 Makes M Makes M 1 Provide M Conversion to Tables to Tables 1 Supplier (Supplier ID, Company Name, Contact Name) Is_in Product (Product ID, Product Name, Units in Stock, Supplier ID) Order Detail (Order ID, Product ID, Unit Price, Quantity, Discount) Order (Order ID, Order Date, Ship Name, Ship Address, Customer ID) Customer (Customer ID, Customer Lname, Customer Fname, Card No, Card Type, Expiration, Address, City, State, Zip) M M Is_included 1 1 Makes M COMM 391, Sauder School, UBC 53 COMM 391, Sauder School, UBC 54 More Examples Examples Questions/Comments See Appendix More exercise to be posted in Blackboard Vista COMM 391, Sauder School, UBC 55 COMM 391, Sauder School, UBC 56 Another Example: Company Employees database Employees database Appendix Appendix You are developing a database to keep track of various employees and projects they are assigned to The database employees and projects they are assigned to. The database is to hold data about the different employees, i.e. their id (E_Id – primary key), Name, Address. The database also needs to keep also needs to keep track on different departments. A on diffe depa department has an ID (Dept_Id – primary key), Dept_Name, and Location. It is also desirable to keep track on the employee managing the department as well as all the employees working for the department (Employees can work for only one department). Finally projects being can work for only one department). Finally projects being worked on at the company need to be recorded along with the employees working on them; a project may have many employees and employees can work on few project at employees and employees can work on a few project at a time. A project has a Name, Location, and Id. COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC 58 58 Another Example Example Let’s build an ER diagram build an ER diagram Let’s try and think of how to put together a music database. E_ID E_Name Employee M Address 1 M works_on manages works_for N 1 Dept_ID 1 Start_Date Project Proj_ID Department Proj_Name Location Dept_Name Location COMM 391, Sauder School, UBC COMM 391, SauderSchool, UBC 59 59 COMM 391, Sauder School, UBC 60 An Example to Challenge You An Example to Challenge You Cust# Name Add. Customer Further Explanations to M:N Explanations to M:N Cust# Name Add. Customer 1 places SKU Desc. 1 places SKU Desc. Total M Order Ord# Total M Order Ord# N includes M products N includes M products 62 COMM 391, Sauder School, UBC COMM 391, Sauder School, UBC 61 Product Entity: Case 1 Entity: Case ProductCode Description LT1 LT1 LT2 LT3 LT3 LT3 Serial# Sony Viao CR 520DN 11521625 Sony Viao CR 520DN 11535515 Sony Viao FW 170D Eee PC 904HD Eee PC 904HD PC 904HD Eee PC 904HD 25462152 54312523 65465542 35432252 LT1 LT2 LT3 LT4 LT5 Product Entity: Case 2 Entity: Case ProductCode Description Sony Viao CR 520DN Sony Viao FW 170D Eee PC 904HD HP Pavilion T5450 Toshiba T5750 T5750 Quantity 3 5 2 1 2 Primary key? Serial# 1 includes Primary key? ProductCode N includes Order M Order products 63 M products 64 COMM 391, Sauder School, UBC COMM 391, Sauder School, UBC Back to the Example Back to the Example Cust# Name Add. Customer 1 places SKU Desc. Total M Order Ord# N includes M products 65 COMM 391, Sauder School, UBC ...
View Full Document

This note was uploaded on 01/03/2011 for the course COMM 290 taught by Professor Brian during the Winter '09 term at UBC.

Ask a homework question - tutors are online