NIT1201 Introduction to Database SystemsAssignment Specifica

NIT1201 Introduction to Database SystemsAssignment Specification (30%) Individual work only. Due date: 23:59pm week 12 Semester 2 2017 Submission via drop box on VU Collaborate only no email submission will be accepted. Introduction The objective of this assignment is for you to put into practice the many different skills that you are learning in this unit into a single cohesive database project.You will be designing a database to meet a specific organisational need. To do this you will work through the various stages of database design including identifying user requirements developing an understanding of the entities required and the relationships between them as well as identifying the business rules associated with the processes that are driving the need for the database. You will then develop appropriate data models and design and implement the database. You will demonstrate that your database implementation is viable through a series of queries and updates on the database.Scenario Western Melbourne University (WMU) requests you to work as a database designer to provide a database solution as part of its course enrolment system. The WMU Enrolment database (WMUED) is expected to store and maintain enrolment activities and records for all students in seven colleges: Arts Science Engineering Business Law Education and Information Technology. WMU accepts two intakes every year one occurs in February and the other is in July. All colleges provide a number of Bachelor and Master courses. The Bachelor course normally take three years to complete for full-time students except Engineering Bachelor courses require four years and Science Bachelor courses need five years. All Master courses take students two years to finish all requirements. Every year students will have two semesters. Each semester a full-time student with 100% study load needs to take four units. That is for a three-year Bachelor course students will complete 24 units; for a four-year Bachelor course students will complete 32 units; and for a five-year Bachelor course will complete 40 units. A master course requires students to complete 16 units in total.When a new student is coming to WMU some basic information will be collected and stored such as full name full home address contact local number mobile number a personal email address emergency contact person information (name contact number relationship). Students also need to provide previous education records including qualification type institute and year of completion.Once a student enrol into a course in February he or she needs to select at most four units (including part-time study load) for Semester 1and another four units for Semester two. Students are allowed to change their units four weeks before every semester starts. If a student enrols in July then he or she only needs to select four units for Semester 2. Re-enrolment for both semesters will be opened next year. Each unit only is only offered in one semester which means Unit X is offered in Semester 1 but not in Semester 2; and Unit Y is offered in Semester 2 then it will not be offered in Semester 1. Once students successfully enrol into the required units he or she will be given a list of the enrolled units and fees to pay for the coming semester. The payment is requested for one semester up to four units at most. Each unit will charge from $2500 to $4000 accordingly. The units are recorded by unit code and unit name. A unit code is assigned to a particular unit only. Same rule is applied on course code as well. A unit will be taught by one lecturer and one or moretutors. The lecturer can work as a tutor too. According to the number of the enrolled students one or more labs will be offered in one unit. Each lab will be taken by a tutor. One tutor can take more than one lab. Each lab has a capability to have at most 30 students. The teaching allocation of all lecturers and tutors will be saved in each semester. Their basic information their class allocation information (including unit to teach class type time starts time ends room location etc.) are collected as well. All students will take lecture at the same time but labs may not start for the same time. According to different units a lecture lasts for one to two hours; and a lab takes from one two or three hours. Each unit requires students to finish a number of assessment tasks including lab exercises tests assignments reports in-class activities practical demonstrations and examinations. The results of all the assessments will be recorded. Grades will be granted based on the final results using the following rules:? N under 50 ? P 50 to 59 ? C 60 to 69 ? D 70 to 79 ? HD 80 or aboveAll students will receive a report of their results of the enrolled units three weeks after the examination. In the report the student information course information unit information the final marks of each unit and corresponding grades will be sent to students via their university email and SMS to their mobile phones. A unit review report will be available for lecturers to access to evaluate student performance. Your task You have been commissioned to develop a database system that is capable of growing as WMUED does.The database needs to keep a record of: All student basic information All staff basic information All student enrolment information All teaching allocation All assessments and results Timetable for all units in both semestersFurther it should be possible to generate a report on: Course enrolment including number of students who enrol newly or continuously Unit enrolment including number of students who enrol newly or repeat Teaching staff allocation including staff information class type time and room location Assessment results of individual students for all enrolled units Student performance of a particular unit including all assessment results and final marks sorted based on grades and surnamesSteps you need to take to develop your database application1. Complete the analysis and design of your database applicationa. List the business rules for your system (do not get distracted by red herrings in the scenario!).b. Identify the entities and relationships in your system. c. Identify the characteristics of the entities in your system. d. Develop an ER diagram to model your system. e. Develop table structures from the ER model. f. Conduct a dependency analysis of the table structures and normalize your tables where appropriate to at least 3NF. g. Create a data dictionary for your database.2. Implement your projecta. Create a database that hosts your application data b. Create tables in your database. These must be consistent with your design. c. Populate all tables with sample data (at least 5 entries in each) d. Create the required views stored procedures etc. to meet the requirements of your systemYou need to be able to demonstrate that your database application meets the requirements detailed in the scenario as well as be consistent with the model you have developed.Submission Requirements Your assignment should be composed of the following parts:1. Project document which includes the following:1.1. A list of the business rules; 1.2. ER Diagram(s) prepared using software such as MS Office Visio or any other ER diagram tool. These should include all necessary information about the entities attributes and relationships. Please provide clear and easy-to-read screenshot of your ER Diagram(s). If you draw your diagram(s) in MS Visio please submit the Visio file too; 1.3. Data dictionary in the format shown in the lecture notes; 1.4. Database design and table structures showing table names as well as any related entity integrity and referential integrity constraints. Include in the table structures attribute data types sizes primary keys foreign keys and any other relevant information; 1.5. Diagrams showing the dependency analysis for each of the tables. You need to demonstrate that all tables are in 3NF. Show the process of normalization including tables in 1NF 2NF and 3NF respectively. Please refer to the examples in lecture notes; 1.6. The SQL code you used to: 1.6.1. Create and populate the database; 1.6.2. Create each of the reports identified in the assignment specification.2. Database implementation in XAMPP 2.1. All the tables of your assignment must be implemented in XAMPP. Please provide screenshots as evidence of your implementation. 2.2. All the tables must be well defined with appropriate primary keys and foreign keys where applicable. Please provide screenshot of table structures. 2.3. All tables must be populated with sample data (at least 10 entity instances