SIT103 – Introduction to Database Design Assignment 2
Trimester 2 – 2015 Due Date: Fri 18th September, 2015
Ultra Laptops
A company wants to set up a new computer hardware supply business to sell to the public online. They have decided to concentrate only on laptops and associated laptop accessories, such as power supplies, portable keyboards etc. They are planning to create a web site showing the products they are going to sell, displaying descriptions, details, prices etc. Customers will then be able to place orders and get the laptops delivered to them.
One of the important aspects of the web site is they want customers to be able to search for laptops in a number of flexible ways. Customers should be able to search on keywords or just view all the laptops sorted in a number of different ways.
Other customers may want to view only laptops in a particular category (eg: ultrabooks only) or laptops only by a specific manufacturer (eg: Apple only). The database therefore has to cater for this need to select and display products based on category and/or manufacturer.
The marketing person in the company had told you that they want similar functionality to existing electronics websites such as Harvey Norman and Dick Smith.
The other major requirement of the site is that some products need to have a ‘related products’ section. So if a customer is looking at a laptop, the site can list the charger that would go with that laptop or other portable devices that would work with the laptop. Not all products, however, would be associated with other products.
In order to help with the development of this venture, you’ve been asked to develop a database with the required data to support this system, including:
• A record of customer information, used for billing and delivery addresses.
• List of all orders placed by a customer, with details of each item purchased, the date & time of the order, the quantity and cost of each item and the total cost of order.
• A stock list of all equipment that can be purchased by customers. Storing the price of the item, the quantity of stock available, the size of the item, and the weight is required.
• The ordering part of the system requires a payment method. The web site will be supporting Credit-Card or Cash-On-Delivery or On-Account methods of payment.
The whole web site will be based on the data stored in this database model. It is important that due consideration is given to provide the infrastructure required to develop this system.
To assist the company, provide a database design consisting of:
• Entity Relationship Diagram.
• Relational Model (showing tables, fields, primary keys and foreign keys).
… assignment specification continues on the next page ….
Higher Level
Create the database tables using SQL statements in your own database. Include
• CREATE TABLE queries – that create each of the tables in your design
• INSERT INTO queries – that insert values into your tables (at least 5 rows per table) Write some queries to test out the database.
• One query should be a simple one involving one table.
• The second query should involve a join (at least 2 tables)
• The third should be a group by query.
• Fourth query that involves a nested query.
Submission
Submit a copy of your data model and optional sql commands, electronically on the course portal, on or before the due date. It is recommended that you submit your assignment as a Microsoft Word document or pdf file.