CC5051NP – Databases Y24 Autumn (Main Sit) – CW QP (1)

1st Sit Coursework 01 Question Paper: Autumn Semester 2024

Module Code: Related Post CET351 Assignment 1 August 6, 2025 CET351 Assignment 1 Template August 6, 2025 Assignment-Cover-Sheet August 6, 2025 CU6008NIVisualEffectsforComputerGraphicsandGamesY24MainSitwithmilestone.CW1QP_a59037b9-3fd6-42be-be54-b3c42ee15936_91672_ July 15, 2025 CC5051NP Module Title: Databases Module Leader: Ms. Pratibha Gurung Tutor(s): Mr. Midhir Rana, Mr. Achyut Parajuli

Coursework Type: Individual Coursework Weight: This coursework accounts for 100% of the overall module grades. Submission Date: First Milestone: Tuesday, 10 December 2024 Second Milestone: Tuesday, 31 December 2024 Final Submission: Thursday, 23 January 2025 Coursework given Week 4 out: Submission Submit the following to the Informatics College’s MST portal before 01:00 PM on the due date: Instructions: ● A report (document) in .pdf format in the MST portal or through any medium which the module leader specifies. London Metropolitan University and Informatics College take plagiarism very seriously. Offenders will be dealt with Warning: sternly.

© London Metropolitan University

PLAGIARISM

You are reminded that there exist regulations concerning plagiarism. Extracts from these regulations are printed overleaf. Please sign below to say that you have read and understand these extracts:

Extracts from University Regulations on Cheating, Plagiarism and Collusion

Section 2.3: “The following broad types of offence can be identified and are provided as indicative examples ….

(i) Cheating: including taking unauthorized material into an examination; consulting unauthorized material outside the examination hall during the examination; obtaining an unseen examination paper in advance of the examination; copying from another examinee; using an unauthorized calculator during the examination or storing unauthorized material in the memory of a programmable calculator which is taken into the examination; copying coursework.

(ii) Falsifying data in experimental results.

(iii) Personation, where a substitute takes an examination or test on behalf of the candidate. Both candidate and substitute may be guilty of an offense under these Regulations.

(iv) Bribery or attempted bribery of a person thought to have some influence on the candidate’s assessment.

(v) Collusion to present joint work as the work solely of one individual.

(vi) Plagiarism, where the work or ideas of another are presented as the candidate’s own. (vii) Other conduct calculated to secure an advantage on assessment.

(viii) Assisting in any of the above.

Some notes on what this means for students:

1. Copying another student’s work is an offense, whether from a copy on paper or from a computer file, and in whatever form the intellectual property being copied takes, including text, mathematical notation, and computer programs.

2. Taking extracts from published sources without attribution is an offense. To quote ideas, sometimes using extracts, is generally to be encouraged. Quoting ideas is achieved by stating an author’s argument and attributing it, perhaps by quoting, immediately in the text, his or her name and year of publication, e.g. “e = mc²(Einstein 1905)”. A reference section at the end of your work should then list all such references in alphabetical order of authors’ surnames. (There are variations on this referencing system which your tutors may prefer you to use.) If you wish to quote a paragraph or so from published work then indent the quotation on both left and right margins, using an italic font where practicable, and introduce the quotation with an attribution.

School of Computing, FLSC

CONTRACT CHEATING

Contract cheating (also known as assessment outsourcing, commissioning or ghost writing) is when someone seeks out another party, or AI generator service, to produce work or buy an essay or assignment, either already written or specifically written for them or the assignment to submit as their own piece of work.

Contract cheating undermines the integrity of the academic process and devalues the qualifications awarded by the university. Students are reminded that academic integrity is a fundamental principle of our institution. Engaging in contract cheating not only impacts the individual’s academic record but also the reputation of the university.

Students are encouraged to seek support if they are struggling with their coursework. The university offers a range of resources, including academic counseling, tutoring services, and workshops on study skills and time management. Utilizing these resources can help students achieve their academic goals without resorting to dishonest practices.

Penalty:

● Failure in the Module: The student must re-register for the same module, and the re registered module will be capped at a bare pass.

● Ineligibility to Continue on the Course: Where re-registration of the same module, or a suitable alternative, is not permissible, the student will not be able to continue on the course. Additionally, the following penalty will be applied to the student’s final award:

o Undergraduate Honors: The student’s final classification will be reduced by one level.

o Unclassified Bachelors: Downgraded to Diploma in Higher Education.

o Foundation Degree: Distinction downgraded to Merit; Merit downgraded to Pass; Pass downgraded to Certificate in Higher Education.

o Masters: Distinction downgraded to Merit; Merit downgraded to Pass; Pass downgraded to Postgraduate Diploma.

Reporting and Consequences:

Instances of contract cheating will be thoroughly investigated, and students found guilty will face the penalties outlined above. It is the responsibility of every student to ensure that their work is their own and to avoid situations that could lead to accusations of academic misconduct.

By adhering to these standards, students contribute to a fair and equitable academic environment, ensuring the value and recognition of their qualifications are maintained. This module is assessed by coursework (100%). For the coursework, the students are required to develop a database for an organization. The coursework / assignment should cover all of the following, but should also not be limited only to these features / functionality:

● Creation of objects — Entities and Attributes

● Creation of Relationship Types

● Identify and include constraints (Such as not null, unique, Supertype, Subtype, etc.) ● Identify and include Primary Keys, Foreign keys and unique keys.

● Normalization of the Relationships (3NF) with Explanation of the process with reasoning.

● Draw 2 ER Diagram, for initial (before normalization) and final (after normalization) with entities and relationships.

This coursework is about the design and implementation of a database for E-Classroom Platform. Part 1. Introduction (15 %)

a. Introduction of the business and its forte (5 marks), description of Current Business Activities and Operations (5 marks)

b. List of Business Rules that derived from the description of Operational Procedures that will be used in the system (5 marks). The rule affects the structure of the database schema.

Part 2. Initial ERD (5%)

The initial Entity Relationship Model should be listed in this section and should include:

a. Identification of Entities and Attributes. The coursework should create the objects and attributes that are related to requirements gathered. Identification and representation of the Primary Keys. (3 marks)

b. Entity Relationship Diagram of the identified Entities with attributes and relationships (2 marks)

Part 3. Normalization (20%)

Normalize the data collected from Unnormalized form to Third Normal Form with valid process description. Produce a set of fully normalized tables for the system as described in the case study. Show clearly all the steps of normalization. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation.

Part 4. Data Dictionary and Final ERD (10 %)

A minimum of 8 Entities must be developed after the normalization. Students must include all the components that are required while designing the final ERD

Part 5. Implementation (10 %)

a. Create relations and tables for the “E-classroom Platform” database with the SQL Command and list the snapshot of its resulting output. Ensure that referential integrity is established between related tables. (5 marks)

b. Populate them with appropriate test data that is relevant to the questions listed below. List the screenshots of the SQL Command used and the overall rows of the table with an image of its resulting output. Enter at least 7 rows in each table. Include the screenshot of the INSERT SQL Statement used to populate table data, along with the TABLE’s CONTENT displayed using SELECT statements. (5 marks)

Part 6: Database Querying (25%)

Submit all appropriate Oracle SQL scripts and screenshots of the resulting image of the output. Make sure each functionality/report is documented separately and clearly mark each piece of output and state its purpose.

Part 7: Critical Evaluation (5 %)

a. Critical Evaluation of module, its usage and relation with other subject b. Critical Assessment of coursework

(Students must provide separate heading as mentioned above)

Part 8: Structure and Formatting (5%)

Students will be awarded 5 marks for structure and formatting

Part 9: Drop Query and Database Dump file creation (5%)

You are required to create and submit a dump file along with your coursework file. (2 marks) Drop tables according to order at the end of coursework. (3 marks)

Part 10: Compulsory viva

Students must attend a mandatory viva session following their coursework submission; failure to attend will affect their grade.

Case Study

Entrepreneur Ms. Mary wants to launch an online application for a college that specializes in managing students, teachers and programs. She plans to establish the “E-classroom platform” to provide both students and teachers with a digital study environment. Your job as a database designer is to help Ms. Mary create and implement a strong database system to support her new e

classroom endeavor. The proposed database system should be able to keep track of all students, programs and modules.

The college offers various programs (BSc in Computing, BSc in Networking, BSc in Multimedia and so on) in different subject areas. Students are enrolled in only one of the various programs. Each program consists of multiple modules which are mandatory for the students. For example, a BSc in Computing might include modules such as Programming, Databases, Professional Ethics, etc. Modules can also be part of different programs, such as the Programming module being included in both the BSc in Computing and the BSc in Multimedia programs, allowing for curriculum flexibility.

Teachers are assigned to teach specific modules. Each Module includes a single or multiple assessments given to students and each assessment will be linked to only one module. Each assessment must have details such as its ID, title, deadline, and weightage. Student’s work is marked, and results are generated, which students can view for each module. The results must detail the components, total marks obtained, and other relevant information to reflect student performance in each module accurately. Modules contain various Resources which are essential for delivering the contents. Resources can have attributes like ID, title, type, duration, etc. Each resource in a module must be completed by students in a predefined sequence. For example, students must complete Resource 1 before being granted access to Resource 2. Only after marking one resource as “completed” can they proceed to the next. This ensures structured and progressive learning.

Additionally, teachers can post announcements/notices for their respective modules, and each announcement must be linked to a specific module.

This database system will enable Ms. Mary’s E-Classroom Platform to effectively manage the complex interactions between entities supporting a structured and dynamic educational environment.

Note: Make sure to declare your attributes in such a way that ensures the successful execution of every information and transaction queries.

Use SQL to solve the following questions.

Information query (10 Marks)

1. List the programs that are available in the college and the total number of students enrolled in each.

2. List all the announcements made for a particular module starting from 1st May 2024 to 28th May 2024.

3. List the names of all modules that begin with the letter ‘D’, along with the total number of resources uploaded for those modules.

4. List the names of all students along with their enrolled program who have not submitted any assessments for a particular module.

5. List all the teachers who teach more than one module.

Transaction query (15 Marks)

1. Identify the module that has the latest assessment deadline.

2. Find the top three students who have the highest total score across all modules. 3. Find the total number of assessments for each program and the average score across all assessments in those programs.

4. List the students who have scored above the average score in the ‘Databases’ module. 5. Display whether a student has passed or failed as remarks as per their total aggregate marks obtained in a particular module. (NOTE: Consider total aggregate marks equal to or above 40 is pass , below 40 is fail)

IMPORTANT

You must use Oracle SQL PLUS to complete your coursework. Use of any other database products such as MS Access, MySQL or Microsoft’s SQL Server for any parts of this work will result in zero marks. Do not forget to drop all the tables after creating the dump file and provide screenshots of the process (code) after creating the dump file (.dmp file).

Submit your work in a zip folder with the naming guidelines provided below in MST portal before the deadline. Your folder should include the documentation of your coursework in PDF format along with the dump file

The naming guideline for zip folder and the document is given below: LondonMetID <space> StudentFullName.zip

Example: 2204567 Kushal Sharma.zip

MILESTONE BREAKDOWN:

Milestone 1 (Tuesday, 10 December 2024):

Part 1. Introduction (15 %)

c. Introduction of the business and its forte (5 marks), description of Current Business Activities and Operations (5 marks)

d. List of Business Rules that derived from the description of Operational Procedures that will be used in the system (5 marks). The rule affects the structure of the database schema.

Part 2. Initial ERD (5%)

The initial Entity Relationship Model should be listed in this section and should include:

c. Identification of Entities and Attributes. The coursework should create the objects and attributes that are related to requirements gathered. Identification and representation of the Primary Keys. (3 marks)

d. Entity Relationship Diagram of the identified Entities with attributes and relationships (2 marks)

Milestone 2 (Tuesday, 31 December 2024):

Part 3. Normalization (20%)

Normalize the data collected from Unnormalized form to Third Normal Form with valid process description. Produce a set of fully normalized tables for the system as described in the case study. Show clearly all the steps of normalization. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation.

Part 4. Data Dictionary and Final ERD (10 %)

Create a data dictionary for all the entities separated in 3NF after the normalization process. Then, create the Final Entity Relationship Diagram including all the entities that have been separated in 3NF. NOTE: Students may create ERD using either Chen’s notation or Crow’s Foot notation, but must involve all the components of ERD.

Part 5. Implementation (10 %)

c. Create relations and tables for the “E-classroom Platform” database with the SQL Command and list the snapshot of its resulting output. Ensure that referential integrity is established between related tables. (5 marks)

d. Populate them with appropriate test data that is relevant to the questions listed below. List the screenshots of the SQL Command used and the overall rows of the table with an image of its resulting output. Enter at least 7 rows in each table. Include the screenshot of the INSERT SQL Statement used to populate table data, along with the TABLE’s CONTENT displayed using SELECT statements. (5 marks)

– End of Paper –