4. Designing Databases

What We Will Cover Announcements Please silence all cell phones and pagers When class is over, please shut down your computer if it is on Elucidations 4.1: Relational Database Fundamentals 4.1.1: About Relational Databases 4.1.2: Primary Keys 4.1.3: Foreign Keys 4.1.4: Defining Relationships 4.1.5: Summary Exercise 4.1: Database Relationships 4.2: Relational Database Design 4.2.1: Introduction to Database Design 4.2.2: Starting the Design Process 4.2.3: First Normal Form 4.2.4: Second Normal Form 4.2.5: Third Normal Form 4.2.6: Summary Exercise 4.2: Design a Database 4.3: Catalog of Database Designs 4.3.1: Common Database Designs 4.3.2: Auctions 4.3.3: Content Management 4.3.4: Order Processing 4.3.5: Shopping Carts 4.3.6: Reservation Systems 4.3.7: Role-Based Access Control 4.3.8: Summary Exercise 4.3: Design Questions 4.4: Proposal Presentations Wrap Up Due Next: A4-Project Database Design (3/16/09) Quiz 4 and Discussion Chapter 5 (3/16/09) Elucidations Homework Questions? A3-Project Proposal (3/9/09) Quiz 3 and Discussion Chapter 4 (3/9/09) Quiz Questions? Login to Blackboard and go to the course homepage For a quick view, click the "My Grades" link on the left menu To view your answers and compare them to the correct answers: Click the "Assessments" link on the left menu Click the View All Submissions button For the assessment you want to review, click the hyperlink under the Attempt column. Questions from last class? ^ top 4.1: Relational Database Fundamentals Learner Outcomes At the end of the lesson the student will be able to: Describe the fundamental characteristics of relational databases Choose a primary key for a relational-database table ^ top 4.1.1: About Relational Databases Relational databases are based on the relational model for database management created by Dr. E. F. Codd Relational databases are the most commonly used type of general-purpose database Part of the popularity of relational databases is their simple data model The term "relational" comes from the mathematical concept of a relation A relation is a set of tuples, which is commonly visualized as a table Relational Data Model In the relational model, data is organized into tables around the concept of an entity An entity is a "thing" in the real world: customers, addresses, products, orders, etc. By design, each table represents only one entity like: Customers table Addresses table Products table Orders table etc. The columns in a table are attributes of the entity An attribute is a property, characteristic or trait of an entity The attributes you chose are the data you need to store in the database For example, attributes of a customer might be: (id, name, address, SSN) A row in a table is a specific instance of an entity In the Customers table: an individual customer If one attribute depends on another, they are linked through "key" columns ^ top 4.1.2: Primary Keys The relational model dictates that each row in a table be unique This requirements allows us to choose any row whenever we choose We guarantee row uniqueness by designating a primary key Choosing a Primary Key Candidate keys: column(s) that contains unique values in each row of a table Each table can have only one primary key The primary key must be selected from among candidate keys After selecting the primary key, other candidates become alternate keys Keys can be simple or composite A simple key is made up of one field A composite key is made up of two or more fields There is no absolute rule for choosing a primary key from candidate keys Some guidelines: Choose a key where the type of data will not change Choose the key with the fewest columns Choose a key that is both simple and familiar to users Check Yourself Consider the following table where the CustomerID column is auto-incremented: CustomerID LastName FirstName Address City Phone  1  Bond  James  221A Baker St.  London  123-456  2  Jones  John  123 Lake St  St. Paul  123-4567  3  Newton  Issac  2345 Apple St.  London  123-457 Which of the following column combinations are candidate keys? (answer) CustomerID Address Phone LastName + FirstName LastName + FirstName + Address LastName + FirstName + Address + City LastName + FirstName + Address + City + Phone CustomerID + LastName + FirstName + Address + City + Phone What is the best choice for the primary key? Why? Making a New Key If an existing column can easily be used as a primary key then you save space in your database However, the best primary key may be a new key A common solution for a primary key is to create a new one like the CutomerID column above None of the other columns is guaranteed to be unique ^ top 4.1.3: Foreign Keys Now that we can choose a primary key let us consider foreign keys Foreign key: a column in a table that contains some of the values of a primary key in a (usually different) table Foreign keys provide the links we need for joining tables in a query From our prior example, assume we chose CustomerID as primary key Now we define a second table Orders as follows: OrderID CustomerID OrderDate  1  1  2002-02-01  2  2  2002-02-01  3  1  2002-02-03 CustomerID is a foreign key in Orders since it refers to a customer described in another table Foreign keys and primary keys must share a common meaning and compatible data type For example, consider the diagram below and consider these questions: What is the data type of Customers.CustomerID? Orders.CustomerID? (answer) Must primary key and foreign key columns use the same name? (answer) Primary Key and Foreign Key Diagram ^ top 4.1.4: Defining Relationships As stated above, we use foreign keys to link tables in a relational database Tables can be related in one of three ways: One-to-many One-to-one Many-to-many One-to-Many Relationships This is the most common type of table relationship Two tables are related one-to-many (1-M) if: For every row in the first table: there are zero, one, or many rows in the second table But for every row in the second table: there is exactly one row in the first table For example in the following two tables: Each row in Orders can have multiple rows of OrderItems But for every row in OrderItems there is only one row in Orders Thus, Orders is related to OrderItems in a 1-M relationship Orders OrderID CustomerID OrderDate  1  1  2002-02-01  2  2  2002-02-01  3  1  2002-02-03 OrderItems OrderID ProductID PriceEach Quantity  1  1  22.50  1  2  4  6.25  3  3  3  4.75  1  3  4  1.00  10 One-to-One Relationships Two tables are related one-to-one (1-1) if: For every row in the first table There is at most one row in the second table True one-to-one relationships are rare in databases They are sometimes used to get around some limitation of DBMS like: Security limitations Performance improvement Exceeding the maximum number of columns allowed (At one time, Access had a maximum of 255) Example security usage: split a table if some columns are used more than others (e.g. Customer -- CustConfidential) You probably do NOT need a 1-1 relationship in your project database tables Many-to-Many Relationships Two tables are related many-to-many (M-M) if: For every row in the first table: there can be many rows in the second table And for every row in the second table: there can be many rows in the first table Many-to-many relationships cannot be directly modeled in an RDBMS Instead, relationships are composed of two 1-M relationships A junction table holds the two 1-M relationships We look at an example a M-M relationship in the next exercise ^ top 4.1.5: Summary In this section, the fundamental characteristics of relational databases A relation is a set of tuples, which is an ordered list Tuples are commonly visualized as a table with each table representing one tuple Usually we organize tables around an entity in the real world Each column in the table is an attribute of the entity Each row is the data for a specific entity Each row in a table must be uniquely identifiable by a primary key These primary keys become a foreign key when stored in another table The foreign key allows us to refer back to the correct row in the referenced table Foreign keys define a relationship between tables Tables can be related in one of three ways: One-to-many One-to-one Many-to-many The most common relationship is one-to-many Check Yourself What is a relational database? What is a tuple? Why does the relational model require that each row be unique? How do you guarantee that a row in a table is unique? How do you choose a primary key? What is a foreign key? What is the relationship (1-M, 1-1, M-M) between each of the following? Customer, Order Product, Order Publisher, Book Book, Author Species, Animal ^ top Exercise 4.1: Database Relationships In this exercise, we look at the relationships between tables. Specifications Create a text file named relationships.txt. Consider the tables below and record the following questions and answers to the questions in the text file: What type of relationship exists between Orders and OrderItems? (answer) What type of relationship exists between Products and OrderItems? (answer) What type of relationship exists between Orders and Products? (answer) What is the primary key of OrderItems? (answer) Submit your relationships.txt file to Blackboard as part of assignment 4. Orders OrderID CustomerID OrderDate  1  1  2002-02-01  2  2  2002-02-01  3  1  2002-02-03 OrderItems OrderID ProductID PriceEach Quantity  1  1  22.50  1  2  4  6.25  3  3  3  4.75  1  3  4  1.00  10 Products ProductID ProductName Price InStock  1  Canvas  22.50  30  2  Brush, Big  4.75  47  3  Brush, Small  3.75  34  4  Oil Paint  6.25  24  5  Acry. Paint  6.50  65  6  Brusher, Hiliner  4.50  10 To help understand the relationships, sketch the tables on a piece of paper and draw a line connecting the relationships between each table. For instance, draw a line between each OrderID in Orders to each OrderID in OrderItems. Do the same between each ProductID in both OrderItems and Products. ^ top 4.2: Relational Database Design Learner Outcomes At the end of the lesson the student will be able to: Use the normalization process Apply normalization to designing a database ^ top 4.2.1: Introduction to Database Design In this section we look at how to design a relational database The benefits of databases designed with the relational model include: Simpler WHERE clauses in your SQL queries Efficient data retrieval, summarization and reporting Efficient data entry, updates and deletions Easier changes to the database design Predictable behavior, since the database follows a well-formulated model How the Process Works The database design process starts out with an initial design After you create the initial design, you apply a series of normalization steps Each normalization step is a test of whether your design has certain desirable characteristics After meeting normalization criteria, you have a design that minimizes duplicate information In addition, you safeguard your database against several types of logical inconsistencies and data anomalies We will look at these steps in more detail in the sections that follow ^ top 4.2.2: Starting the Design Process Before designing, take time to analyze the system you are trying to model You need to examine the data and conditions of the problem Consider using some of the following techniques to understand the problem: Paraphrase the problem: write a statement about the problem in your own words Ask questions about the problem and try to get answers to the questions Draw a diagram to help visualize the problem Look for similarities to previous problems you or others have solved Separate the parts: break a big problem down into smaller problems You will need a good understanding of the problem to make decisions about your database design Creating an Initial Design A good starting point is to make a list of data items to store and organize the items into categories Each category becomes a table and the data items for the category are columns of the table After identifying the table, you choose a primary key Note that your project might be like other database projects If so, you can save time by starting your design from these existing designs However, the final design depends upon what you want to do with your data Refining the Design As we develop our design we are faced with a series of choices: How many tables will there be and what will they represent? Which columns will go into which tables? What relationships do we need between the tables? Answering these questions is part of database normalization Normal forms are a series of tests that you apply to your database Meeting these test criteria removes redundancies and structures data to prevent inconsistencies from arising Each higher normal form achieves a better, more efficient design We will cover the first three as they are the most basic forms ^ top 4.2.3: First Normal Form The purpose of the first normal form (1NF) is to eliminate any repeating groups of data 1NF says that all attributes must be atomic (indivisible) There can exist only one value per field -- not an array or list of values Non-atomic Values The following table violates 1NF because column values are not atomic What is the difficulty with this arrangement of data? OrderID CustomerID Items 1  1  1 Canvas 2 2 3 Oil Paint 3 1  1 Brush, Small, 10 Oil Paint How do you retrieve data such as the quantity of Oil Paint ordered? (answer) Repeating Groups 1NF prohibits repeating groups -- even if the repeating groups are in different columns What are some of the difficulties with this arrangement of data? OrderID CustomerID Qty1 Item1 Qty2 Item2 1 1 1 Canvas     2 2 3 Oil Paint     3 1 1 Brush, Small 10 Oil Paint Any query would have to search all QtyX and ItemX columns What if more items are needed than the available groups of columns? When do you stop adding columns? Achieving First Normal Form The following table meets the requirements of 1NF OrderID ItemNbr CustomerID OrderDate Quantity ProductName  1  1  1  2002-02-01  1  Canvas  2  1  2  2002-02-01  3  Oil Paint  3  1  1  2002-02-03  1  Brush, Small  3  2  1  2002-02-03  10  Oil Paint What is the primary key of this last table? (answer) What is Atomic? One question you may have is, "Where do you stop breaking up data into smaller pieces?" The word atom comes from a Greek word that means: indivisible When applied to a database column, atomic means that the data cannot or should not be divided further Consider an address stored in a database for a pizza delivery guy To make a delivery, he just needs a street number and address in a single column For his purposes, the address in the following table is atomic OrderNumber Address 123 221A Baker St. 123 221B Baker St. 124 2345 Apple St. Now consider a table with addresses for a realtor The realtor may want to query all the houses on one street Thus, the realtor needs separate columns for the street number with a table like this: StreetNumber StreetName Price 221A Baker St. 325000 221B Baker St. 499000 2345 Apple St. 1499000 More Information First normal form: Wikipedia article ^ top 4.2.4: Second Normal Form The purpose of the second normal form (2NF) is to remove redundant data 2NF only applies to tables having primary keys with multiple columns The requirements of the 2NF are: The table is in 1NF Every non-key column is fully dependent on the entire primary key If you can look up some data in your table using only part of the primary key, the table does not meet 2NF. The following table is in 1NF but not 2NF -- why not? Note that the primary key is a composite of both OrderID and ItemNbr OrderID ItemNbr CustomerID OrderDate Quantity ProductID ProductName  1  1  1  2002-02-01  1  1  Canvas  2  1  2  2002-02-01  3  4  Oil Paint  3  1  1  2002-02-03  1  3  Brush, Small  3  2  1  2002-02-03  10  4  Oil Paint If we only know the OrderID, can we find the CustomerID or OrderDate? (answer) Thus the CustomerID and OrderDate do not depend on the entire primary key We see repeated CustomerID and OrderDate values for the same order number This means that these columns belong in a separate table We achieve 2NF by breaking the data into two tables: Orders and OrderItems Orders OrderID CustomerID OrderDate  1  1  2002-02-01  2  2  2002-02-01  3  1  2002-02-03 OrderItems OrderID ItemNbr Quantity ProductID ProductName  1  1  1  1  Canvas  2  1  3  4  Oil Paint  3  1  1  3  Brush, Small  3  2  10  4  Oil Paint OrderID is a foreign key in OrderItems and is used to rejoin the tables The foreign key allows us to put the tables back together again in a query More Information Second normal form: Wikipedia article ^ top 4.2.5: Third Normal Form The purpose of the third normal form (3NF) is to remove columns that do not depend on the primary key 3NF requires: The table is in 2NF All non-key columns are mutually independent All the attributes must directly depend on the primary key In the words of William Kent: The relation is based on the key, the whole key and nothing but the key. One way to identify potential 3NF problems is to look at your table and see if any columns would require updating if another column in the table was updated Then you decide if the columns in question depends on "nothing but the (primary) key" Calculated Columns An obvious example of not meeting 3NF is a calculated column For example if you have the columns: "subtotal", "taxes" and "total" The "total" column is calculated from the other two columns It is better to leave a calculated column out of a table entirely because: It saves room in the database Avoids having to update the calculated field when other fields change When you need a calculated result, you calculate it within your query Other Dependencies Dependencies that are not the result of calculations can also exist in a table Can you spot the non-key dependencies in the following table? (Hint: you can look up a value using a column that is not a primary key) OrderItems OrderID ItemNbr Quantity ProductID ProductName  1  1  1  1  Canvas  2  1  3  4  Oil Paint  3  1  1  3  Brush, Small  3  2  10  4  Oil Paint Such non-key dependencies cause problems when adding, updating, or deleting rows Consider if you had 1000 rows of OrderItems for the Brush,Small: You have both a ProductID and ProductName for all 1000 records What if you change the name of the Brush,Small to Brush,Petite? After you delete all your old orders, how will you know that a ProductID of 3 means Brush,Petite? To correct the problems, move any column that does not depend directly on the primary key to another table In this case, move ProductID and ProductName into a separate table ProductID becomes the primary key of new table Products OrderItems then keeps a foreign key of ProductID to look up the ProductName when needed You can easily rejoin the tables using a SQL query Here are the two new tables meeting 3NF: OrderItems OrderID ItemNbr ProductID Quantity  1  1  1  1  2  1  4  3  3  1  3  1  3  2  4  10 Products ProductID ProductName  1  Canvas  2  Brush, Big  3  Brush, Small  4  Oil Paint  5  Acry. Paint  6  Brusher, Hiliner More Information Third normal form: Wikipedia article ^ top 4.2.6: Summary In this section we looked at how to design a relational database You start the design by modeling entities from the real world such as a: Person Place Thing Concept Event Once you have an initial design, you normalize your design to achieve an optimal structure that: Removes redundant data Minimizes the chances of data becoming inconsistent over time You achieve 1NF by removing any repeating groups of data You achieve 2NF by: Moving attributes that are not dependent on the entire primary key to another table Creating relationships between the new tables and the original table through the use of foreign keys You achieve 3NF by moving any column that does not depend on the primary key to another table After meeting 3NF, all non-key columns depend on the primary key, the whole primary key and nothing but the primary key Further Information Database Normalization: from Wikipedia Database Normalization And Design Techniques Check Yourself What is the purpose of the first normal form? How do you achieve the first normal form? What is the purpose of the second normal form? How do you achieve the second normal form? What is the purpose of the third normal form? How do you achieve the third normal form? ^ top Exercise 4.2: Design a Database In this exercise we design a database. Background Dr. Joe and Dr. Ed are running a small family practice. They have been saving patient visits information in a spreadsheet and want you to design them a database instead. Under strictest confidentiality, they have provided you with the sample data shown below. Patient Name Patient Address Dates Visited Attending Physician Insurance Name Insurance Address James Bond 221A Baker St. 01/1/1, 01/12/25 Dr. Joe, Dr. Ed HealthCo 123 Big Bucks Way John Jones 123 Lake St 01/12/30 Dr. Joe NyetCare 234 Nopay Lane Isaac Newton 2345 Apple St. 01/2/3, 02/2/4 Dr. Ed, Dr. Ed FurtherCheck, HealthCo 345 Seymour St, 123 Big Bucks Way Specifications Create a text file named doctors.txt. Consider the above data and create a design and normalize it to 3NF. For each table you end up with, record the design in the text file in the following form: tablename(field1, field2, ... , fieldn) Underline the primary key field in each table. You may use the text convention of starting and ending the field with an underscore ( _ ) character. tablename(_field1_, field2, ... , fieldn) Indicate the presence of foreign-key fields, normally indicated using a dashed underline or italics, by using an asterisk ( * ) to start and end the field. tablename(field1, *field2*, ... , fieldn) Submit your doctors.txt file to Blackboard as part of assignment 4. One possible answer ^ top 4.3: Catalog of Database Designs Learner Outcomes At the end of the lesson the student will be able to: Apply common database designs to solve their own design problems ^ top 4.3.1: Common Database Designs Relational databases have been around for years It is probable that other people have faced similar database design problems as you need for your project In this section, we consider several examples of database design Each section explains an approach to a common design problem Many more designs are available in the recommended book: Data Modeling, G. Lawrence Sanders, boyd & fraser publishing company, ISBN 0-87709-066-1. Students are free to start their project database design from these samples However, even using these patterns, you will still need to normalize your database design ^ top 4.3.2: Auctions An online auction offers members the opportunity to buy and sell items The Members table stores information about people buying and selling Includes both buyers and sellers The Items table keeps track of items on the auction block The Bid table links buyers with items for sale The Ratings table records the ratings placed by buyers or sellers for a transaction. Members(Username, Password, LastName, FirstName, Email) Bid(ItemID, Buyer, Price, QuantityWanted, BidTime) Items(ID, Seller, Category, Title, Description, Quantity, StartPrice, BidIncrement, LastBidReceived, CloseTime) Rating(ID, ItemID, Buyer, Seller, BuyerRating, BuyerComment, SellerRating, SellerComment) ^ top 4.3.3: Content Management Many web sites feature articles and news stories Each web page has a code that is stored in the Pages table The heading for each page is stored in the Pages table as well Assumes that a story will appear on only one page The Stories table contains the content submitted by writers Which page a story appears on is controlled by the foreign key Page Each contributing writer has an account in the Writers table Editors use the same table Permissions to publish on a page is controlled by the WritersPages table WriterPage table used to check which writers can access which pages Usually an editor to prevent mayhem Pages(PageCode, Heading) Stories(ID, Writer, Page, Headline, StoryText, Picture, Created, Modified, Published) Writers(Username, Password, LastName, FirstName, Email) WritersPages(Username, PageCode) ^ top 4.3.4: Order Processing This design is for a Web based e-commerce site Orders are placed by customers via web browsers The Customers table stores information about customers However, we store customer addresses in the Addresses table Separate table because customers can have many addresses Addresses are related to Customers using the CustomerID foreign key We keep information about an order in the Orders table The OrderItems table stores the information for each item ordered Allows variable numbers of items for each Order The Products table contains a list of products items Customers(ID, LName, FName, Email) Addresses(ID, CustomerID, Address, City, State, Zip, Country) Orders(ID, CustomerID, DatePlaced, Status) OrderItems(OrderID, ItemNbr, ProductID, Quantity, PriceCharged, Status) Products(ID, Name, Description, Path2Image, PriceEach, InStock, Weight) ^ top 4.3.5: Shopping Carts Internet-based e-commerce site Products are viewed by customers via web browsers Selected items are placed in a "shopping cart" for eventual purchase The ShoppingCarts table keeps information about items selected SessionID is a unique number assigned to track each user Similar to an OrderItems table Do not need a separate table for the session, since it is just an identifier ProductID provides the rest of the primary key The Products table contains a list of products available The SessionID is external data provided by PHP ShoppingCarts(SessionID, ProductID, AddDate, PriceCharged, Quantity) Products(ID, Name, Description, Path2Image, PriceEach, InStock, Weight) ^ top 4.3.6: Reservation Systems A system to allow people to reserve inventory for a period of time Inventory can include: equipment, rooms, seats for events, etc. The Customers table stores information about people reserving inventory The Inventory table stores information about each item people can reserve Use the Reserves table to reserve inventory items for a period of time Starting can be a timestamp or an integer number for day of year and the like Duration is the number of units of time for which customers can reserve inventory Customers(Username, Password, LastName, FirstName, Email) Reserves(Customer, InventoryID, Start, Duration, Status) Inventory(ID, Description) ^ top 4.3.7: Access Control Most access control is based on a username and password You can store this information in a single table like: Users(Username, Password, LastName, FirstName, Email) Occasionally, a project may need a more complicated system with multiple levels of access The best approach for these types of complicated systems is usually role-based access control Role-Based Access Control A role-based access control subsystem More sophisticated access control than most projects need The Users table stores information about the people who can access the system The Role table stores information about each role people can play Use the UserRole table to assign users to their roles The Operations table stores information about each operation people can perform Use the RoleOperation table to assign role-players to the operations they can perform Users(Username, Password, LastName, FirstName, Email) UserRole(Username, RoleID) Roles(ID, Name, Description) RoleOperation(RoleID, OperationID) Operations(ID, Name, Description) ^ top 4.3.8: Summary In this section we looked at some database designs These designs may be useable as a starting place for your project database design However, even if you use some of these designs, you still need to normalize your project database design Check Yourself You can sometimes reuse database designs from one project to the next True False AnswerTrue The Physician's database of exercise 4.2 resembles which one of these design patterns? Content Management Order Processing Reservation System None of these AnswerC Why might the role-based access control be unsuitable for your database application? AnswerToo complicated for most projects ^ top Exercise 4.3: Design Questions In this exercise, we consider the database design patterns listed above. Specifications Create a text file named patterns.txt. Consider the designs we reviewed in this section and record the following questions and answers to the questions in the text file: Are database designs reusable in another application? Why or why not? Which design does the physicians database of exercise 4.2 resemble? Which of the above designs may be usable in your project? Submit your patterns.txt file to Blackboard as part of assignment 4. ^ top 4.4: Proposal Presentations The presentation should consist of the following parts: Statement of the Problem Identification of the Expected Output List of the Data to Store Try to keep the presentation to 5 minutes or less Feel free to leave (or stay) after your presentation You can present to the instructor alone after all the public presentations are done ^ top Wrap Up Due Next: A4-Project Database Design (3/16/09) Quiz 4 and Discussion Chapter 5 (3/16/09) When class is over, please shut down your computer if it is on ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 14 2009 @17:18:48