Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Project

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

Project Detail:

In this project you will be provided the set of specifications to create an entity-relationship diagram and design accompanying table layout using sound relational modeling concepts and practices. Relationships among 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 the 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 lastly decided to automate their record keeping with a relational database.
You sit down with Mom and Pop to talk about their business, and watch their operation for about a week. You find out quickly that the video and a DVD are both copies of a movie kept in the separate plastic case which is rented out. They have many copies of each movie they rent; hence there are many 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. Video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.

Price of a DVD for a movie may 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.). Single distributor might provide several different types of movies in both video and DVD format. It is possible to get the same movie from multiple distributors, and at different wholesale prices.

All video and DVD has a unique identification number which 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 give 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 should be no limit to the number of video and/or DVD copies of a movie which 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 should 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. Rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

Using this information, you must provide the following in the submission:

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

2) Begin the database logical design by identifying entities, relationships between entities, and entities' attributes as we have done in this course. Sketch 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." ERD should not have any many-to-many relationships between entities. Ensure that all relationships between entities are one-to-many to facilitate the construction of relational database tables.

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

4) Make sure your work is neat and legible.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M9666

Have any Question? 


Related Questions in DBMS

Suppose that you have designed a database for morgan

Suppose that you have designed a database for Morgan Importing that has the following tables: EMPLOYEE (EmployeeID, LastName, FirstName, Department, Phone, Fax, EmailAddress) STORE (StoreName, City, Country, Phone, Fax, ...

Question talk about the importance of pulling data from

Question : Talk about the importance of pulling data from worksheets into a single sheet also the ways excel could be a solution to a complex challenge. The response must be typed, single spaced, must be in times new rom ...

Systems analysis project scenic routes operates a bus

Systems analysis project Scenic Routes operates a bus company that specializes in travelling on secondary roads, rather than Interstate highways. Their slogan is: "It Takes a Little Longer, But It's Scenic." The firm nee ...

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Question create an erd for the following scenarioa small

Question: Create an ERD for the following scenario. A small company ABC wants a database to keep track of internal company information. Â Given the following information create an ERD. The ABC Company has several departm ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

Select from e d pwhere edeptddept and dcitypcity and

Select * From E, D, P WHERE E.dept=D.dept AND D.city=P.city AND D.budget>1M AND P.priority=A; Assuming relations have the same size and uniform distributions, what is the best plan with Nested Loop joins only? Write it a ...

Assignment question - write and run sql statements to

Assignment Question - Write and run SQL statements to complete the following tasks Part A - DML 1. Locate the record in the vendor table that does not have a value for the attribute V_STATE 2. Find the customers whose ba ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As