7003ICT: Database Design - Conceptual Design of the Database - Computer Science Assessment Answer

Computer Science Assessment Answer

1. Explain whether your tables are (at least) in 3NF. If a table is not in 3NF, modify your ERD or do normalization. If you choose to use non-3NF tables, provide the reasons why.
2. Assign appropriate data types and lengths to all attributes. Include necessary integrity constraints such as domain constraints, null value constraints, and other important integrity constraints.

1. ERU operates stores in the following cities: Brisbane, Gold Coast, Melbourne, Sydney, Newcastle, Adelaide and Perth. Stores are referenced by store number. ERU also keeps store name, contact details (address, phone, fax, email) for each store.
2. ERU has implemented a new system of supervision for its stores. Each store is managed by an employee as a store manager. Each store is also assigned a supervising store where all training is conducted, and server applications and help desk functions are located.
3. For each employee ERU keeps a record of their employee id, first name, last name, the date they started working for ERU, the current store they work in, and their tax file number. Previous work experiences including the company name, position, start date, and end date are also recorded, if available.
4. For each DVD ERU keeps a record of their DVD number, title, length, release date, type (movie or music) and description. For each music DVD, it records the number of tracks, music type and the name, country and date of birth of the musicians. For each movie DVD, it records the director and the actors, including their first name, last name, and country as well as the category (e.g., action, children, comedy, thriller).
5. Each DVD has various prices in its lifetime. It has the price that it first started off with and then may have one or more price changes. These price changes may be increases due to increased supplier cost, GST, etc. or decreases due to clearance, damaged stock, result of sale activity or meeting competition prices. No two price changes can have the same effective from date. ERU also likes to record the reason for the price change.
6. An inventory of the number of each particular DVD kept at each store needs to be maintained. ERU keeps track of the quantity of each DVD that is on order, as well as the number currently available in each store (quantity on hand).
7. Customer details (first name, last name, and contact phone number) are always taken/checked at each order. A customer is referenced by a customer number. If a customer does not exist on the customer list the next available customer number is allocated and details are entered later.
8. If a DVD is not currently available in the store, customers may still place orders. A customer may order more than one DVD at any one time, and they may order multiple copies of the same DVD. ERU also records the date DVD arrives and the date that it is picked up by the customer. Note that these dates may be different for each DVD.
9. ERU runs a loyalty program named ERUclub. Qualified customers can become ERUclub members. Each club member is provided with a membership ID and a loyalty point account, where members can earn and redeem points as follows: for every whole dollar spent in any ERU store, 10 loyalty points will be earned. Loyalty points can be redeemed for future purchases when the points balance reaches 1000: there will be a $10 discount for every 1000 points redeemed. ERU needs to maintain the points account for each club member, including the point balance, date and time, amount, as well as the corresponding DVD purchase order of each point account transaction (top-up or redemption). In addition, the date joined and an email address will be recorded for each club member.
10. ERU understand that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record them.

• The EERD contain all necessary entities, relationships, attributes.
• Correct entity/relationship types (strong or weak) are used.
• Each strong entity has a PK
• Each relationship has the two types of constraints
• Correct class-subclass relationships are identified, if any
• There are no redundant entities, relationships or attributes (In particular, no foreign key attributes are present in the entities Please do not confuse ER diagram with schema diagram, and confuse entities with tables.

• You have mapped your EERD to tables correctly using the appropriate rules.
• You have marked/listed all primary key and foreign key constraints. Please do not just copy your entities as the tables...usually you need more tables than entities because you may need to map some relationships into tables, and multi-valued attributes into tables. Please do not combined this task with task 3.

• All your tables are in 3NF (and otherwise convincing reasons are provided to justify the use of non-3NF tables).
• An appropriate data type and length for each attribute.
• Integrity constraints such as null value constraints and important domain constraints are present.
• Other important constraints/business rules are identified.

• Script contains the drop table commands and runs without errors.
• Tables are created and data inserted appropriately.
• Queries are written correctly and produce the correct results.

This Computer Science Assessment has been solved by our Computer Science experts at onlineassignmentbank. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.