ERD

CMIS 320 Project 1 Mom and Pop Johnson video store ERD

This project allows students to demonstrate their skills in the area of designing relational databases to satisfy specific business rules and requirements. The deliverables for this project include an Entity Relationship Diagram and detailed documentation describing the database design and structure.

Project 1 Details:

In this project you will be provided a set of specifications to create an entity-relationship diagram and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow-on project. The following paragraphs provide the background and summary of the business requirements.

You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices.

Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.

Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

Using this information, you should:

1. Determine and list your entities. Then describe fully the relationships between entities via pairs of sentences that indicate the two components of the total relationship in both directions between the entities. Relationships may be unary, binary, or ternary with respect to entities. You should not have any many-to-many relationships.

2. Begin the database logical design by identifying the entities, relationships between entities, and entities' attributes as we have done in this course. Use the same entity/relationship diagram (ERD) notation as used in class for entities, attributes, and relationships. Sketch your ERD by hand or a drawing program (e.g., Visio, PPT, SQL Modeler …) on one single 8-1/2" x 11" page (8-1/2" x 14" maximum), labeled "Mom and Pop Johnson Video Store Database E/R Diagram." Your ERD should not have any many-to-many relationships between entities. Make sure that all relationships between entities are one-to-many to facilitate the construction of relational database tables. 

3. Complete the logical database design and start the physical database design by creating metadata (i.e. documentation) that describes the table(s) created from each entity and the column(s) created from each attribute in the ERD. Attributes should be self-describing. Particular attention will be given to the proper specification of all primary key (via "PK") and foreign key (via "FK") columns in the table layouts. These should match your ERD exactly.  Begin these descriptions on a page labeled "Proposed Database Tables and Columns based on E/R Diagram." All tables must be 3rd Normal Form. Indicate any and all assumptions that were made.

4. Make sure your work is neat and legible.

Submissions:

1. You should submit a well-organized Word document that includes your entity list, ERD, and database design description. This document should also include all assumptions and your lesson learned during your project.

2. Submit in your WebTycho portfolio in the Project 1 area before the due date. Please contact the instructor before the due date if you have any questions or concerns.

Learn More

I.C.U Blinds Database Design

A customer goes to the store to buy blinds.  This customer is served by one salesperson in a department.  This salesperson can have many customers.  An installer is scheduled to measure the customer's house for blinds.  The installer can have many customers but each customer is served by only one installer.  When a customer buys a blind, an invoice is created for the transaction.  Installers and sales people report to a manager.  A manager may manage many employees.

Based upon a visit to the store and interviews with several store employees, consider the following:
Customer data must be stored including name, address, email address, and phone number.  Sales person and installer data must be stored including name, address, phone number, hire date, and manager ID.  Manager information is similar but the database must store information about the employees they manage.  Each employee may also have dependents eligible for the company-provided health coverage.  Information to be stored about dependents includes name and date of birth.  Each blind has a SKU, price, and description associated with it.  Each invoice may have multiple blinds on it.  Invoices include date purchased, item quantity, and calculated fields such as item subtotals (qty * price), and grand total.

You have been asked to design a database for “I.C.U Blinds” based upon your expertise in databases.

CHOOSE ONE OF THE FOLLOWING (AND ONLY ONE)

Option #1 (Rock)
Draw the dependency diagrams which step through the normalization process (1NF, 2NF, and 3NF).  Denormalize as needed and show your final dependency diagram.  If you do denormalize, explain the reasoning behind each case.  Please follow the formatting guidelines as per the normalization homework assignment we did.  What you should turn in, is similar to the Chapter 5 assignment.

Option #2 (Hard Place)
Break down the scenario into the component relationships.  For each relationship, write the associated one liner, and the 4 sentences.  Combine these relationships and in Visio, draw the ERD associated with your solution.  Be sure to note all attributes, PKs, FKs, cardinality, optional and mandatory relationships, strong/weak relationships and entities.  What you should turn in, is very similar to the Chapter 4 assignments.

Tips and tricks
Carefully consider each question and do not be afraid to add attributes – some will be needed (e.g. primary and foreign keys), some need to be inferred – (e.g. purchase date for the invoice), and some will be calculated (e.g. totals).   As we have learned in class, remember in your answers that good solutions take into account the trade-offs between number of tables, the amount of data redundancy, and data to be entered for each transaction.

Learn More

COM 330 Chapter 6 Assignment

1. Using the STUDENT table structure shown in Table P6.4, do the following.

Table P6.4 Sample STUDENT Records
Attribute Name    Sample Value    Sample Value    Sample Value    Sample Value    Sample Value
STU_NUM    211343    200128    199876    199876    223456
STU_LNAME    Stephanos    Smith    Jones    Ortiz    McKulski
STU_MAJOR    Accounting    Accounting    Marketing    Marketing    Statistics
DEPT_CODE    ACCT    ACCT    MKTG    MKTG    MATH
DEPT_NAME    Accounting    Accounting    Marketing    Marketing    Mathematics
DEPT_PHONE    4356    4356    4378    4378    3420
COLLEGE_NAME    Business Admin    Business Admin    Business Admin    Business Admin    Arts & Sciences
ADVISOR_LNAME    Grastrand    Grastrand    Gentry    Tillery    Chen
ADVISOR_OFFICE    T201    T201    T228    T356    J331
ADVISOR_BLDG    Torre Building    Torre Building    Torre Building    Torre Building    Jones Building
ADVISOR_PHONE    2115    2115    2123    2159    3209
STU_GPA    3.87    2.78    2.31    3.45    3.58
STU_HOURS    75    45    117    113    87
STU_CLASS    Junior    Sophomore    Senior    Senior    Junior

c. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

d. Draw the Crow’s Foot ERD.

Load the data from table P6.4 into an Access database. Add 5 additional students (make up your data) following the table structure.
Create a query in Access that list the students who have a Statistics major.
List the students who have a GPA of 3.45 or higher in a report.
Report the students who have the same advisor.

Submit c and d as an attachment, attach your Access database.

Learn More

You are required to use any available to tool to design and develop an ER diagram and write SQL script. The accepted file formats are MS Word or PDF file format.

Learn More

Coffee Merchant Enterprises (CM) and Home Warranty Corporation (HWC) Data Warehouse OLAP database

The purpose of this assignment is to promote students analytical skills in analyzing a case scenario and utilizing the given information and resources to design an ER diagram and develop an OLAP database.

1. Case Scenario
Coffee Merchant Enterprises (CM) sells coffee from a variety of different countries to consumers in the US.  Their sales are all on-line.  Their OLTP database for handling their day-to-day operations is shown in the ER Diagram below:

CM wants to make data more available to their marketing analysts so that they can develop more effective marketing programs.  They want to accomplish this goal without slowing down the performance of their existing OLTP system.  They have decided to create a Data Warehouse to provide the information that the marketing analysts need.  Your task is to design the database for the Data Warehouse.
The marketing analysts have asked for the ability to see sales data by product (inventory), consumer, employee who placed the order, and by various date variables including date, week, quarter, year, day of the week, and season.  They do not need to see every sales but would need to see product sales summarized for each product on a daily basis.


2. Case Scenario
Consider the following ER Diagram for the OLTP database you designed from Home Warranty Corporation in the Case Project for Module 11.  Your design may have differed from the one below, but this is the design that HWC decided to use.

HWC has decided to develop a Data Warehouse so that they can analyze costs to assist in making a number of business decisions.  For example, they want to determine if certain customers are costing more because they may be too hard on their appliances or if certain appliances that they cover tend to break down more than others, thus incurring more cost.  Your task is to design the database for the Data Warehouse. The business managers have asked for the ability to see costs by customer, appliance, employee, and by various date variables including date, week, quarter, year, day of the week, and season.  The need to see this cost information for every Service Call and need know the labor cost, material cost, and sum of both.

Learn More

IM300 Week 4 Integrity and Constraints Hotel Database Part 3

Resources: SR-bi-003 – Hotel Database, the results from SR-bi-003 – Hotel Database Parts 1 & 2, Oracle® Database Express® and Microsoft® Visio® Virtual Desktop.

Complete Service Request SR-bi-003 - Hotel Database–Part 3.

Normalize the entities and attributes to at least the third normal form.

Use Microsoft® Visio® diagramming tools to create an entity-relationship diagram (ERD) that demonstrates the logical design of your normalized database.

Create and alter tables in the city’s Oracle® Database Express® database to implement the ERD design.

Generate a new DDL script.

Create a fax cover sheet addressed to the project manager for the ERD and a newly generated DDL script to show your progress on the project.

Learn More

CS251 Fundamentals of Database Systems Week 4 IP The Microsoft Access Database

Using Microsoft Access create the tables and relationships defined in your data model. Your Microsoft Access Database at this point should include the following:

The Tables as defined in your Entity Relationship Diagram.
The Relationships between the tables.
3 Defined Queries. One of the queried needs to be created to show the data from the Many-to-Many relationship (or include three tables) originally identified from the initial requirements.
Take a screenshot of the ERD and the Queries.

Add the Screenshots to the section in your template titled: The Microsoft Access Database.

Learn More

Create a Microsoft Access database using Riordan Manufacturing’s 2005 sales figures. Normalize the tables to the third normal form. Create relationships among tables using the relationship tool. These relationships must include settings for referential integrity and cardinality. Create an extended ERD of your newly created database, using Microsoft Word, PowerPoint, or Visio. Include primary keys, foreign keys, fields, relationships, and cardinality in the ERD. Learn More

Types of Relationships and Sample Databases

Part 2
Come up with an original example of a one to many unary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 3
Come up with an original example of a ternary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 4
Using MySQL Workbench – create a data model for on Adult Baseball league which supports all of the following data requirements:
Teams
    A team has a unique number associated with it
    Team Name
    Team City
    Each team has more than one player associated with it

Players
    Each Player has a unique number
    A player may play or have played for more than one team
    The number of years and batting average is tracked for each team the player played for.

Coaches
    In this league, all coaches are also players.
    A Team can have more than one coach.

Bats
    A team can have multiple bats
    Each bat is identified by a unique serial number.

For each of the three models above, you should export your model as a single page PDF and submit the resulting files.

Learn More