E/R Diagram, Sql Script And Front-End

5CC507 Databases 1 Databases - 5CC507 Assignment Project Scenario 2022-2023 5CC507 Databases 2 Athens Module Leader Konstantinos Katsifis Email: k.katsifis@medcollege.edu.gr Phone: +30 210 8899632 Office Hours: Monday 15:00-17:00 Key dates and details Assessment Type: Databases Project Assessment weighting: 60% Word Count N/A Learning Outcomes: 2 Submission Method: Classter Date Set: 21/11/2022, 23:59 Submission Date: 15/01/2023, 23:59 Description of the assessment The students will design, implement, test and evaluate a database system designed to support an enterprise scenario using industry-standard techniques, tools, technologies and best practices. The project consists of 3 parts: E/R diagram, SQL script and front-end (Microsoft Access). Students will design and implement the database system, consisting of an E/R diagram, normalisation (minimum of 3NF) and SQL script with commented source code, working database and front-end for the database. This coursework assesses learning outcome two (LO2): Be able to design and implement robust, scalable database systems that can underpin enterprise applications. Being able to design and implement a database system is a fundamental skill in the discipline and useful in some of your future assessments Assessment Content This coursework requires you to complete a database project on a specific scenario. The project consists of three main parts: 5CC507 Databases 3 - Report documentation, including E/R diagram, in which clearly describe the design of your database, including all the necessary entities and relationships, cardinality and normalisation. - SQL code to create the tables of your database, commented and with named primary and foreign keys (using software that automatically create the SQL script is NOT allowed and will result in failing this assessment). The resulting database must meet the requirements of the scenario, without allow update anomalies. The database must be built on Microsoft SQL Server and the front-end in Microsoft Access. - A data-entry front-end in MS Access, which meets the requirements of the scenario, with prompts and labels correctly spelled and easy-to-read, and allows non- technical users to update all relevant database tables. We will explore the content of the coursework together in a synchronous session on Week 1 and again when the scenario will be announced. All components of this coursework assignment MUST be submitted electronically via classter by the due date and time: Database project to be submitted by 15th January 2023 via Assesments in classter. A submission point will be available around that time. You must submit it as ONE zip file that contains the following:  Report documentation including E/R diagram, and any other text material you consider necessary in PDF/DOCX format.  SQL script: source code required to create the database.  Front-end in MS Access. Please note: Submission in any form other than a single zip file (e.g. rar, multiple files, etc) for the database project will NOT be accepted and you will automatically fail this assessment. If you fail to include all the mandatory files, your final grade will be reduced by 20% for each missing part. VERY IMPORTANT NOTE: The database must be built on Microsoft SQL Server and the front-end in MS Access. If this is not the case, the highest grade you can receive on this assignment will be 30%. If your MS Access is not saved in the Desktop on your personal VM, you will get 0 on this component. Scenario A research team has asked you to create a database for a project on movie production companies; the project aims to use machine learning, neural networks and other methods to extract information about the situation of movie production companies in Europe and the health of this sector for a set of specific countries, including the UK. The data analytics application resulting from this project – which you DO NOT have to develop; your job is to develop the central, server-side database that underpins it – has 5CC507 Databases 4 been commissioned by a research institute (which shall remain nameless), and it is intended to be open source, and therefore available to anyone.1 Basically, it is a machine learning application that would run on a database with the aim to identify the correlation between different aspects of the sector, including funding opportunities and development of new production companies or studios.2 The database records every production company in Europe, including the name of the company, the address, ZIP code, city, country, type of the company (e.g., non-profit organisation), number of employees and net worth (calculated as total assets minus total liabilities). Every production company has its name registered with one and only one local government authority (for example, Companies House in the UK) on a specific date; each company can have many shareholders. The authority typically requires information about all the shareholders, including town of birth, mother’s maiden name, father’s first name, their personal telephone number (only one), national insurance number (each country in Europe has a similar unique ID), and passport number. Also, the registration procedure has a cost associated with it (e.g., 12£ in the UK). The database also records the employees’ data for each company: each employee is assumed to work for a single production company. Due to the complex structure of movie production companies and the need for various skills and professions, employees are categorised into crew and staff. The crew consists of three main groups: the actors, the director(s) and those who work on other jobs relevant to the filming (producers, editors, production designers, costume designers, composer, etc.). All other employees belong to the staff group, including those responsible for HR, advertising, etc. Employees are identified by an employee ID, first name, last name and an optional middle name, date of birth and start date. Also, each employee has their contact details recorded, whether it is a single phone number or multiple, with a description associated with each of them. Each employee has a single email address, too. Members of the crew are paid hourly, and this is recorded in the database as well as a bonus that depends on their contract. Actors get a bonus for each day of work and another bonus for each scene completed; directors get a bonus at the end of the shooting; crew members that work in other jobs relevant to the filming get a bonus at the end of the shooting, and they have their role recorded as well (e.g., producer or costume designer). Staff members have the monthly salary and the working hours (e.g., full time 9-5). Furthermore, each staff member belongs to a specific department (e.g., advertising), which is located in a given building at a given address (both recorded in the database). The database records all movies from each production company. More specifically, for each movie the following information is recorded: a universal unique movie code 1 This scenario is purely hypothetical. It is not real; in case you are worried that students are being exploited for free database development labour. 2 It doesn’t have a name, because I couldn’t think of one 5CC507 Databases 5 (similar to the ISBN for books), the title of the movie, the year and the first release date (different release dates are not important and should NOT be recorded). Also, the database records each member of the crew that is part of the movie, and the role they have in the movie: each crew member can play a single role or multiple roles in the same movie, and each role has a description associated with it. For example, in each movie there can be a single protagonist or more than one, the same actor can play one or several roles, or even have a cameo. One of the aims of the project is to provide insights on the impact of funding and grants within the movie industry. To this end, the database should be able to record all the funding that each production company receives. This must include the name of the grant, the funding body (e.g., the government of a given country or European Union grants such as the ERDF), the maximum amount for that grant and the deadline to submit a proposal. Then, for each company the database must record the date of the application to a given grant, the amount requested, the outcome (successful/unsuccessful). A grant can be given to a single production company or shared among several. Finally, once the database is ready, the project will run a set of machine learning algorithms to perform high level data analysis based on the different grants and their corresponding impact with the aim to investigate the impacts of such funding against a list of criteria. No additional information is provided at this stage from the project. Test Data No test data is given for this scenario – although some sample data are provided for demonstration purposes in the above description. It is expected that you think of reasonable data to insert into your database. To assess the validity of the data, you can test it on the requirements listed below. You can also test if your front-end works properly by manually entering the data into your database via MS Access. Requirements In the spec, the requirements are numerated from 1 to 5, as the scenario was not given at that time. The details of each requirement are provided in the following: 1. Each production company may have received one or multiple grants, and grants can be shared by more than one company. 2. It is possible for each employee to have more than one telephone number. Each telephone number has a description associated with it (e.g., personal, or work). 3. Each production company is registered only once but can have many shareholders. 4. Each employee can either be a member of the crew OR a staff member. Each crew member can be an actor OR a director OR have another role. Each staff member belongs to a department. No duplication of data is allowed. 5. Each crew member may be part of one or more movies in a single role or many. 5CC507 Databases 6 Assessment Rubric The assessment rubric on the next page shows the complete criteria of the CW and how you will be assessed. We will explore the content of the rubric together in a synchronous session on Week 1 and again when the scenario will be announced. When the assessment is returned you will receive a digital version of the rubric showing how you performed against each criterion. You will also receive short individual written feedback that highlights both the strengths of the work and your key areas for development. There will be an opportunity to discuss these with your personal academic tutor. 5CC507 Databases 7 5CC507 Databases 8 Anonymous Marking You must submit your work using your student number to identify yourself, not your name. You must not use your name in the text of the work at any point. When you submit your work in classter you must submit your student number within the assignment document and in the Submission title field. For example, if your student number is 100123456, you should name your file 5CC507-100123456.zip when submitting it. Assessment Regulations The University’s regulations, policies and procedures for students define the framework within which teaching and assessment are conducted. Please make sure you are familiar with these regulations, policies and procedures. Formative There will be a formative opportunity to support this coursework. You will be provided with formative feedback during the tutorial classes towards the end of the module. This will help you practice and prepare for the final submission.