- This assessment is for students to develop the capacity to design and implement a
- This assessment requires students to identify business rules, create a data dictionary, create an Entity-Relationship diagram, and develop an Oracle database using
- This is an individual assessment
- The project documentation submitted should include business rules, data dictionaries, ER diagrams, SQL source code such as AT2.sql, and SQL spool files such as AT2.txt. See ‘Assessment Task 2 – Submission Instruictions.pdf’ for
Unit Learning Outcomes
- Of the three Unit Learning Outcomes (ULOs) of this unit SIT103, this assessment task will focus on the last two ULOs. These are:
- ULO 2 – At the end of this unit students will be able to evaluate data models and apply data modelling techniques to capture the data aspects of real-world
- ULO 3 – At the end of this unit students will be able to design and develop relational databases by using SQL and a database management
- The assessment of this task (Project Documentation and Database) will indicate whether students can partially attain these unit learning
- Read these instructions, the following scenario and
- Attempt as many tasks as
- Place your name, ID and answers in your
- Please note that MS Word (docx) files, txt files, and SQL script files are expected to be submitted, do not submit PDF files.
- As there will be several files that you will submit, you will place all files in a folder. You might like to name this folder AT2 – John Smith 215123456. You should then and ZIP that folder to produce a file such as AT2 – John Smith 215123456.zip. You will submit this ZIP
A1E is a fictitious retail company using www.a1e.com as their main URL. Imagine their web site providing customers with information related to many categories of electronic products: computers, televisions, audio receivers, mobile phones, cameras, and more. Also, there are many subcategories of products such as computers might be divided into Apple, Dell, HP, Microsoft, Lenova, and so on. In general, A1E uses a database containing data about products, categories, customers, and product reviews.
Please use the following table schemes. This is not a complete list as several additional tables are required to design and develop this database.
Product (PID, manufacturer, name, model, imageFilename, price) Characteristic (CID, name, shortDescription)
Feature (FID, name, shortDescription) Detail (DID, name, shortDescription) Category (CatID, name)
Subcategory (SubCatID, name) Customer (CID, name, address, mobile)
Review (CID, PID, author, subject, text, recommendation, quality, value) Score (SID, value, description)
In addition to the above database information.
- Each product has many characteristic that are used to create a product overview. Each characteristic can be used for many products, this would make it easy to find all products with that kind of
- Each product has many key features. Each feature can be used for many products, this would make it easy to find all product with that key
- Each product has a list of details. Each detail can be used for many products. This would make it easy to find all product with the same or similar
- Each product might belong to many categories. For example, a gaming desktop might belong to the computing category and also the gaming category. Each category might have many products.
- Each product might belong to many subcategories. For example, a laptop might belong to the laptops subcategory and also the gaming laptops subcategory. Each subcategory might have many
- Each category can have many subcategories, and each subcategory might belong to many categories.
- A1Es website allows customers to make reviews. There can be many reviews. Each customer can review many products, and each product can be reviewed by many
- Each review must contain both quality and value scores that are provided by the reviewer. A five point scoring system is used. Each numeric score is related to a small description of a few words such as:
- 1 very poor
- 2 poor
- 3 good
- 4 very good
- 5 excellent
For the above scenario:
Task 1. Determine the business rules for all tables including junction tables.
Task 2. Develop a data dictionary for all tables including junction tables.
Task 3. Develop an ER diagram for all tables including junction tables. Clearly label all entities, primary and foreign keys, relationship connectivity and cardinalities.
Task 4. Develop an SQL script, say AT2.sql, to run on Deakin’s Oracle database which:
- removes previous tables related to this question, e.g., drop …
- creates these tables including junction tables, e.g., create …
- inserts data into appropriate tables, e.g., insert …
- uses the spool command to start recording to a file, e.g., spool /home/username/AT2.txt
(please replace username with your Deakin login name)
- turns on the echo, e.g., set echo on;
- displays the name and price of each product that has Wi-Fi, e.g., select …;
- displays the following data about each product in the “4K Ultra HD TVs” subcategory where the price > $4000:
- manufacturer, name, image filename, and price, e.g., select …;
- name, and the average of the review quality and value scores, e.g., select …;
- the number of reviews, e.g., select …;
- displays the following data about the Samsung Galaxy S9+ 256GB (Coral Blue):
- manufacturer, name, model, image filename, price, e.g., select …;
- average of the review quality and value scores, e.g., select …;
- the number of reviews, e.g., select …;
- all characteristics (name and description), e.g., select …;
- all key features (name and description), e.g., select …;
- all details (name and description), e.g., select …;
- all review data (subject, text, recommendation, quality, value), e.g., select …;
- turns off the echo, e.g., set echo off;
- turns off the spooling, e.g., spool off;
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:dmxyzl003 工作时间:全年无休-早上8点到凌晨3点