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 = mc2 (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
