ERD

Individual Assignment: ERD and Table Specifications Resources: Huffman Trucking Virtual Organization Review the information currently housed in Huffman Trucking’s database, in the Entities and Attributes for Fleet Truck Maintenance file on the virtual Organizations site. Create a Microsoft® Visio® Logical ERD, based on the Entities and Attributes for Fleet Truck Maintenance document, and any additional information needed to normalize the database. Ensure that your entities are properly normalized. Properly annotate the entities, primary keys, foreign keys, and relationships in your ERD. All relationships should be one-to-many. Use the crow’s feet notation for your ERD. Every entity should be related to at least one other entity. Create table design metadata specifications in a Microsoft® Excel® spreadsheet based on your ERD. Indicate primary keys, foreign keys, and new attributes as in the entity relationship diagram. For each table in your metadata, indicate the table’s name, the columns in the table, the data types for the columns, the sizes of the columns, the primary keys, the foreign keys, the required/NOT NULL columns, the default values, and the range of legal values for the columns. Learn More

Individual ERD Creation Project The following assignment is based on the database environment chosen and discussed in the Week Two Individual Assignment. Use a Microsoft® Visio® diagram to create a detailed ERD using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty’s feedback. · Use a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships. Learn More

Individual Normalization of the ERD The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment. Your database project must meet the following assessment requirements: Design and develop a database using professional principles and standards. · Provide a logical and physical design of the database. · Use relational database software application to develop database. · Provide an entity relationship diagram. · Normalize the database. · Generate and provide test data. Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF). Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables. Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access®database. Submit the ERD and final database to the appropriate Assignment link. Learn More

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

The purpose of this assignment is to allow you to practice designing and developing an ER diagram by using an appropriate tool.

This assignment requires you to develop an ER Diagram for a database that is used to maintain basic information concerning the hours spent on projects by employees in a small advertising company. A customer may have many projects and an employee may have worked on many projects. Each project is for just one customer.

The tables needs to include the following:
• Customer(id, name).
• Employee(id, name, department id, salary).
• Project(id, title, customer id).
• EmpProj(employee id, project id, hours).

Primary keys and foreign keys need to be identified as well. Draw the ER Diagram using any tool that you want. The easiest is probably a word processor, but you could also use a drawing tool such as Visio.

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