CC5051NI Databases – Oracle Database – Islington College

1st Sit Coursework 1 Question Paper Autumn Semester 2023 

Module Code:  CC5051NI Module Title:  Databases Module Leader:  Yunisha Bajracharya

Coursework Type:  Individual Coursework Weight:  This coursework accounts for 50% of the overall module  grades. Submission Date: Monday, 15th January 2024 Coursework given  Week 4 out: Submission  Submit the following to the Islington College’s MST portal Instructions: before the due date (before 01:00PM on the due date): ● A report (document) in .pdf format in the My second  teacher platform. London Metropolitan University and Islington College take  Warning: plagiarism very seriously. Offenders will be dealt with  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 unauthorised material into an examination; consulting unauthorised  material outside the examination hall during the examination; obtaining an unseen  examination paper in advance of the examination; copying from another examinee; using an  unauthorised calculator during the examination or storing unauthorised 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 offence 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 offence, 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 offence. 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.

2 

School of Computing, FLSC 

This module is assessed by coursework (50%). 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 / functionalities: 

● 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 Gadget Emporium. Part 1. Introduction (15 %) 

a. Introduction of the business and its forte, 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. c. Identification of Entities and Attributes. The coursework should create the objects and  attributes that are related to requirements gathered. (5 marks) 

Part 2. Initial ERD (5%) 

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

a. List of the created objects — Entities and Attributes 

b. Identification and representation of the Primary Keys, Foreign Keys. 

c. Entity Relationship Diagram of the identified Entities with attributes and relationships  (5 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. 

3 

Part 4. Final ERD (10 %) 

A minimum of 6 Tables must be developed after the normalization.  

Part 5. Implementation (10 %) 

a. Create relations and tables for the “Gadget Emporium” 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 two paragraphs) 

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%) 

Drop tables according to order at the end of coursework. (3 marks) 

You are required to create and submit a dump file along with your coursework file. (2 marks)

4 

Case Study 

Entrepreneur and electronics enthusiast Mr. John wants to launch an online store that specializes  in selling electronic devices and accessories. He plans to establish the “Gadget Emporium” online  marketplace to provide both private consumers and business organizations with a large selection  of electronic devices. Your job as a database designer is to help Mr. John create and implement a  strong database system to support his new e-commerce endeavor. The proposed system should be  able to keep track of all customers, products and orders.  

Business Rules: 

1. Product Management: The system should handle the details of electronic gadgets and  accessories, including product names, descriptions, categories, prices, and stock levels.  Each product must be of only one category and each category can have one or many  products.  

2. Customer Categories and Discounts: The system should be able to keep track of all its  customers. Customers should be categorized as Regular (R), Staff (S), and VIP (V). Each  category is entitled to a different discount rate on product purchases, such as 0%, 5% and  10% respectively. Each customer’s address must also be stored which helps in the delivery  process. 

3. Order Processing: Customers can browse and purchase one or more electronic gadgets  online. The system must record the details of each order, including the products purchased,  quantities, unit price, and total order amount. An order can have multiple products and any  one type of product might be included in multiple orders placed by various customers. 

4. Vendor Management: Maintain records of vendors or suppliers providing electronic  gadgets and accessories. Each product should be associated with a single vendor. Each  vendor can supply one or more products. 

5. Product Availability and Inventory Management: Track real-time product availability to  prevent overselling and maintain accurate stock levels. A product must have inventory  details like stock quantity or availability status. 

6. Payment Processing: The system should integrate with various payment gateways to  facilitate secure and seamless transactions of each order. Payment options must be either  cash on delivery, credit/debit card or e-wallet. Each order detail must have one payment  option. 

7. An invoice must be issued once the customer checks out their order after confirmation  which must include the details of order, customer and payment details (with discount).

5 

Use SQL to solve the following questions. 

Information query  

1. List all the customers that are also staff of the company. 

2. List all the orders made for any particular product between the dates 01-05-2023 till 28- 05-2023. 

3. List all the customers with their order details and also the customers who have not ordered  any products yet.  

4. List all product details that have the second letter ‘a’ in their product name and have a stock  quantity more than 50. 

5. Find out the customer who has ordered recently. 

Transaction query  

1. Show the total revenue of the company for each month. 

2. Find those orders that are equal or higher than the average order total value. 3. List the details of vendors who have supplied more than 3 products to the company. 4. Show the top 3 product details that have been ordered the most. 

5. Find out the customer who has ordered the most in August with his/her total spending on  that month. 

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 and provide screenshots of the  code after creating the dump file (.dmp file). 

Submit your work in a zip folder with the naming guidelines provided by RTE. Your folder  should include the documentation of your coursework in PDF format along with the SQL  scripts with accurate question number as file name and dump file. Submit your report to  RTE before the deadline. 

– End of Paper –

6