I have a Database class project that I need help with. I am to create a data dic

Question

I have a Database class project that I need help with. I am to create a data dictionary for this system "COSPrint".

This is the background of the problem:

The IT Center prints posters for other university offices on campus. These posters are custom created posters which others create and the COS IT Center merely prints. NOTE: They do not sell posters in bulk, merely print and charge for those posters. These posters are priced based on printed area (square feet), extra media (paper, etc.) used, and a flat processing fee per poster. The media used (currently fabric, glossy paper, and matte paper, but could be extended with other options) determines the rate/fee for each price component listed in the previous sentence. Printed area and extra media are both a charge per square foot. The processing fee is a flat fee per media type. You will need an inventory of various media types. Extra media is defined as the area to the left and right of the printed area on the roll of paper used. Currently, all media rolls are 60 inches wide, but different media that may be in inventory over time could come in different roll widths.

One or more posters may be charged to a single invoice. Invoices that are generated from the system need to have the following: unique identifying number, customer info (name, department, contact info), invoice date, vendor rep (staff member completing the job), subtotal amount, tax amount, shipping amount, total amount, itemized description of poster(s), and payment instructions (custom text area). Note that not all of the information above should be stored in the database (HINT: think derived data) and not all of the information should come from a single entity (use relationships!)

I need help making a data dictionary for this system. I need to make sure the data is normalized as well.

Explanation / Answer

To have normalized database, this should be organized in below tables:

Master tables are as below

1. Media_Table

2. Inventory_Table

3. Price_Table

4. Customer_Table

Below are transaction tables

5. Invoice_Head_Table

6. Invoice_Detail_Table

Now the datafields in each of these tables are as below:

In Media table, have a unique_ID or SKU No or itemcode for each media.

Media_Id:Unique Id, Primary key

Media_Name:Character of length 20 values will be fabric, glossy paper, and matte paper,

Media_Rollsize: No. e.g. 60

Media_RollSizeUnit: Char(10) e.g. Inches

Extra_Media

Additional_Info

Inventory_Table

This will have Media_ID which has a referential integrity with the above table and another field Quantity which is a number.

Price_Table

For each media type various rate information is stored.

Media_id Printed_Area_Rate   Media_Rate   Flat_Fee
Char(20)   Real Real Real

Customer_Table

Cust_ID Cust_name, department, contact info

Cust_Id is a primary key here and rest of the fields are character with specified length

Invoice_Head_Table

Invoice_No Invoice_Dt Cust_Id Vendor _Rep Paymet_term Sub_Total Shipping_Amount Tax_amount Total

Invoice_Detail_Table

Media_Id, Quantity Printed_Area_Rate   Media_Rate   Flat_Fee Printed_Area_Value Media_Value Item_Total

Printed_Area_Value=Printed_Area_Rate*Quantity

Media_Value = Media_Rate*Quantity

All the amount fields can be real number type here.

The above ideas can be firther expanded for which fields are not null, and where are all the referential integrity etc.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.