Systems and Database Design Assignment: Database Design & Implementation (Online Store)
General Assignment Information
Before a database can be implemented and used, it must be designed in a way that ensures it is appropriate for the task at hand. Tools such as Entity-Relationship Diagrams and Data Dictionaries assist in designing and communicating the structure of a database. Once a design has been finalised, the database can be implemented in a DBMS. The Data Definition Language commands of SQL are used to create the database and its tables, attributes and constraints, after which the Data Manipulation Language commands can be used to manipulate data within the database. This assignment takes you through the design and implementation process, using Microsoft SQL Server.
Task 1 - Database Design
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 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.
Some marks are also awarded for presentation and notation.
Your complete database design should consist of a list of assumptions, logical and physical ER diagrams and a data dictionary. This should be submitted as a single PDF document. Make sure that your assignment includes the unit code, assignment number/name, year and semester and your name and student number on the first page.
Ensure that your completed database design is in PDF format, and open the PDF file before submitting it to ensure that your diagrams appear as intended.
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
• 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.
- The store wishes to ensure that each customer has a different email address.
• 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
• Item details must be recorded. This includes an item number, name, description and price.
• 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.
- The only category details required are a category number and category name.
• 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
• Details of orders made by customers must be recorded. This includes an invoice number, the date of the order and customer number of the customer who made the order.
- The order details should contain two foreign keys referencing the address table - one for the delivery address and one for the billing address.
- 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.
• 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.
General Information and Guidelines
The information above describes all of the entities, attributes and relationships required in the database design. Some minor details, such as the cardinality of some relationships, have been omitted. It is up to you to make (and state) any assumptions you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your tutor.
It is recommended that you use auto-incrementing integers as the primary key for most entities in this scenario, although a compound primary key may be appropriate in some tables such as the one keeping track of which items are in which categories.
Be sure to specify the most appropriate data type (and length, where applicable) for each attribute in your data dictionary. Note that when you are storing a date/time, it should be stored as a single column - do not split the date and time into two columns unless there is a very good and necessary reason to do so. Where sample data is provided, make sure it fits into the columns lengths you use.
Read the scenario details several times to ensure that your database design incorporates all the elements described. If you desire feedback on your work in progress, send it to your tutor.
Additional Requirements
If you are in CSI5135, the following additional requirements apply. If you are in CSG1207, you do not need to do implement these requirements (but you are encouraged to do so if you want).
Ensure that your database design (and implementation in Task 2) incorporates the following:
• The name of each category must be unique
• Customer email addresses must contain a "@" symbol
• The quantity of an ordered item must be between 1 and 100, with a default of 1
• For security reasons, customer passwords will be encrypted using "bcrypt" before storing them in the database. You must do a small amount of research to determine an appropriate data type and length for the password column so that it is able to contain bcrypt hashes.
- It is up to you whether you actually put bcrypt hashes into the column when writing your sample data in Task 2 - it will not be used in any of the queries.
Several of these requirements will be implemented using CHECK constraints when creating the database. Specify these CHECK constraints in your data dictionary in a way that clearly describes what is being checked, or using the actual SQL code required to create the constraints.
Task 2 - Implementation
Once your database has been designed, it is time to implement it in a DBMS, populate the database, and then manipulate the data via queries. The deliverables of this task are three files containing SQL statements. We will be using Microsoft SQL Server 2014 or above - your SQL scripts must run in the same environment used in the unit/labs.
Create your scripts as three ".sql" files, with the filenames listed in the following headings. Templates for the script files are provided with this assignment brief - please use them. Format your code for readability, and use comments for headings and to provide further detail or information about your code if needed.
As each of the script files will contain numerous SQL statements, it is very useful to be aware of a particular feature of SQL Server Management Studio (SSMS): If you have selected some text in a query window, only the selected text will be executed when you press the Execute button.
This makes it much easier to test a single statement, or even part of a statement, within a script file.
You are required to create all of the scripts detailed below. Please take note of the file names and use the template files provided with this assignment brief.
Filename: create.sql
This file is a creation script, similar to the "company.sql" file (Module 5) used to create the company database.
Database Creation & Population Script
Produce a script to create the database you designed in Task 1 (incorporating any changes you have made since then). Be sure to give your columns the same data types, properties and constraints specified in your data dictionary, and be sure to name tables and columns consistently. Include any suitable default values and any CHECK or UNIQUE constraints that you feel are appropriate.
Make sure this script can be run multiple times without resulting in any errors (hint: drop the database if it exists before trying to create it). You can use/adapt the code at the start of the creation scripts of the sample databases available in the unit materials to implement this. You will need to follow an appropriate creation order when creating your tables - you cannot create a table with a foreign key constraint that refers to a table which does not yet exist.
Once you have created your database, it is recommended that you use SSMS to create an ER diagram and use this to verify that your implementation matches your design. This can be done by right clicking on the "Database Diagrams" folder of the database in the Object Explorer in SSMS.
Following the SQL statements to create your database and its tables, you must include statements to populate the database with sufficient test data. You are only required to populate the database with enough data to make sure that all views and queries return meaningful results. You can start working on your views and queries and write INSERT statements as needed for testing as you go.
For example, imagine you are working on a query which joins data from two tables and only displays the rows that meet certain criteria, then orders the results by a certain column. To test this, you will need to insert some data in both of the tables, making sure that some of the rows meet the criteria and others don't, and making sure that the column used for ordering contains a range of different values. Once you have inserted this data, you can test that your query works.
The final create.sql should be able to create your database and populate it with enough data to make all views and queries return meaningful results.
Make sure all referential integrity is observed - you cannot add data to a column with a foreign key constraint if you do not yet have data in the table it refers to. Remember that if you are using an auto-incrementing integer, you cannot specify a value for that column when inserting a row of data. Simply pretend the column does not exist when inserting data - do not try to specify a value for it.
Attachment:- Systems and Database Design Assignment.zip