CSG1207: Systems & Database Design- Database Design & Implementation Assignment

Internal Code: 1HBHC

Database Design & Implementation Assignment:

Task: Task 1 – Database Design (20 marks) Your first task is to design a database for the scenario detailed on the following pages. Your final database design should comprise of approximately 8 entities. State any assumptions (2 marks) you have made regarding your database design at the beginning of the database design document. Do not make any assumptions that significantly change the structure of the scenario, as this may make Task 2 of the assignment more difficult or inapplicable. Only make assumptions that influence your database design. If you are unsure about an assumption you wish to make, ask your tutor. Once you feel you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram (4 marks) and a corresponding physical ER diagram (4 marks) to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in Lecture 3. It is recommended that you draw your diagrams on paper first, in order to find a layout that is clear and can be created in an electronic format. Lastly, create a data dictionary (8 marks), with an entry for each entity in your database. The entries should list the name of the entity (table), a description of its purpose, a list of attributes (columns), important information about the attributes (e.g. data type, null/not null, identity, default values…), and details of any constraints applied to attributes. List the entries in your data dictionary in an appropriate table creation order that can be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, a data dictionary should contain all the information needed to implement a database. Use the data dictionary in Lecture 4 and the data dictionary of the “company” database (Module 5) as examples Scenario Details You are required to design and create a database for an online store. The database must encompass the customers, items, categories of items and the orders made. You have the following information about the way the store operates: Customers & Addresses 1. Customer details must be recorded. This includes a customer number, first name, last name, email address, password and a column containing either a “Y” or an “N” to indicate whether the customer wishes to receive the store’s email newsletter. 2. The store wishes to ensure that each customer has a different email address. 3. The store wishes to implement a “referral system” to reward customers who tell others about the store. Therefore, customer details should also include a “referrer” column, which will contain the customer number of the customer who referred them, if applicable (not all customers are referred by someone). Customers can define addresses which are stored in the database. A customer can define multiple addresses, and each address is associated with a single customer via their customer number. As well as specifying the address, customers can (optionally) specify a name for the address, e.g. “Home”. An address number is used to uniquely identify each address. Items & Categories 1. Item details must be recorded. This includes an item number, name, description and price. 2.A list of item categories must be recorded, and the database must keep track of which items are in which categories. All items are in at least one category, but can be in several of them. 3. The only category details required are a category number and category name. 4. To receive more relevant newsletters, customers can specify which categories they are interested in. Which customers are interested in which categories must be stored in a table of the database. The newsletter categories are the same as those which are linked to items. Orders 1. Details of orders made by customers must be recorded. This includes an invoice number, the date and time of the order and customer number of the customer who made the order. 2. The order details should contain two foreign keys referencing the address table – one for the delivery address and one for the billing address. 3. A customer therefore needs to define at least one address in order to make an order. The same address can be used for both the delivery and billing address. 4. For each order, the database must also record details of ordered items and the quantity ordered. Each order must contain at least one item, and an item can be in multiple orders.