Data modeling and normalization, Software Engineering

Assignment Help:

The task in this project is to design a normalized database that could be used to collect and store information for a video rental outlet.

The database should be structured to support tasks such as:

tracking rentals by title and by customer;

producing reports, such as rentals due on a certain date;

categorizing rentals by media type (e.g., Blu-Ray, DVD, Game).

The assignment does not require you to produce examples of the reports listed, but only that the database be correctly structured to
allow the query and reporting features of a DBMS, such as Access, to work properly.

You will need to collect information about several titles in each category, which can be done online, at your local video store, or from your personal media collection. You will be given an initial, un-normalized field list of data items to be tracked, which you will normalize into the appropriate separate tables.

The database students will prepare in this project is somewhat simplified in comparison to a real-world application. For example, only brief customer address information is included, to reduce the amount of data input required for the project. We have also simplified the example in terms of structure; ignoring, for example, the complexities of a real-world video outlet, such as varying rental periods for different products.

The initial field list does not include all of the appropriate key fields for the tables that will be created, so suitable key fields should be added to the tables as required.

This project is most easily completed using MS-Access; for a detailed discussion of the other options for completing the Project, please see "Alternatives to Using MS Access" below.

PROJECT DETAILS

The following is a list of essential data items to be recorded. As noted above, additional fields may be required as keys for the tables you will create from these fields.

date of rental

due date

media item title

media item category

category description

category code

customer ID#

customer lastname

customer firstname

customer address

customer phone#

If only a single table were made from all the fields listed above, the result would be a system in which every rental would have to include duplicate customer and media item details. In addition to wasting input time duplicating data, such a system would also be highly prone to error, as any change to customer data (a change of address, for example) would also result in different records showing different content for the same fields, unless every historical record for that customer was updated. The relational database model was designed to solve those problems by identifying key data entities, separating them into their own tables, and relating the tables using foreign keys. (The foreign key is usually the primary key from one table, placed in one or more other tables to create linkages.)

This allows for customer details, for example, to be recorded once, in a table reserved for that purpose, and referenced in other tables by a unique identifier (key), such as Customer ID#. Your task is to create a simple relational database from the field list above. The required tables are Customers, Media, Categories, and Rentals; each of which should be properly provided with a primary key, and each of which should be related to other tables as required. All four database tables should be populated with records as follows:

Customers-at least 10

Categories-three records, one describing each of the

media types: DVD, Blu-Ray, or Game Media-at least 30 records, representing a selection of DVD movies, Blu-Ray movies, and Games.

Rentals-at least 20 records.


Related Discussions:- Data modeling and normalization

What are the various kinds of system testing, What are the various types of...

What are the various types of system testing? 1. Recovery testing - is intended to check the system's capability to recover from failures. 2. Security testing - verifies tha

Write definition of software process, Write definition of software process....

Write definition of software process. Software process is termed as the structured set of doings that are required to develop the software system.

Problem frams, . We discussed the importance of framing a problem in order ...

. We discussed the importance of framing a problem in order to understand the problem better and be able to develop a solution more quickly and easily. In this homework, you are

Micro cds-isis application, Micro CDS/ISIS Application: In libraries t...

Micro CDS/ISIS Application: In libraries there are a number of repetitive operations. If one Miens the procedures for ordering, receiving, processing and circulating a book, o

Discuss the design steps of the transform mapping, Discuss the design steps...

Discuss the design steps of the transform mapping. Re-examine the fundamental model. Re-examine and refine the DFD for the software. Determine the DFD has either the

What is detailed COCOMO model, Q. What is Detailed COCOMO model ? The d...

Q. What is Detailed COCOMO model ? The detailed COCOMO model is able to estimate the staffing cost and duration of each of the subsystem, development phases and modules. It per

Advantages of using mobile agents, Question: (a) Give three advantages...

Question: (a) Give three advantages of using mobile agents. (b) Distinguish between Static Itinerary Static Order (SISO) and Static Itinerary Dynamic Order (SIDO) mobility

What is throw-away prototyping, Throw-away prototyping Objective: ...

Throw-away prototyping Objective: The principal objective of this model is to validate or to derive the system needs. It is developed to decrease requirement risks.

Define the behavioural model, Define the Behavioural Model It defines o...

Define the Behavioural Model It defines operational behaviour of the system.  In this model, numerous operations of the system are represented in pictorial form. Tools used to

What are the proper testing strategy, What are the proper testing strategy ...

What are the proper testing strategy It is necessary to develop a proper testing strategy to ensure all possible scenarios are covered and that all error trapping techniques ar

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd