CSG1207/CSI5135: Systems & Database Design- Database Design & Implementation- Online Store- IT Assignment Help

Internal Code: 1HBII

Database Design & Implementation:

Task 1: 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. Some marks are also awarded for presentation and notation (2 marks). 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. Please 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. Note: The scenario for this assignment is the same as the one from Task 3/4 of Assignment 1. Be sure to incorporate any feedback received in Assignment 1. Task 2: 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.