XJTLU Entrepreneur College (Taicang) Cover Sheet
Module code and Title DTS106TC: Introduction to Database
School Title School of AI and Advanced Computing
Assignment Title Assessment Task 001 (CW): Individual Coursework
Submission Deadline 17 May 2023 at 5:00 PM
Final Word Count NA
If you agree to let the university use your work anonymously for teaching and learning purposes, please type “yes” here. Yes
I certify that I have read and understood the University’s Policy for dealing with Plagiarism, Collusion and the Fabrication of Data (available on Learning Mall Online). With reference to this policy I certify that:
My work does not contain any instances of plagiarism and/or collusion.
My work does not contain any fabricated data.
By uploading my assignment onto Learning Mall Online, I formally declare that all of the above information is true to the best of my knowledge and belief.
Scoring – For Tutor Use
Student ID
Stage of Marking Marker
Code Learning Outcomes Achieved （F/P/M/D）
(please modify as appropriate) Final
Score
A B C D
1st Marker – red pen
Moderation
– green pen
IM
Initials The original mark has been accepted by the moderator (please circle as appropriate): Y / N
Data entry and score calculation have been checked by another tutor (please circle): Y
2nd Marker if needed – green pen
For Academic Office Use Possible Academic Infringement (please tick as appropriate)
Date
Received Days late Late Penalty ☐ Category A
Total Academic Infringement Penalty (A,B, C, D, E, Please modify where necessary) _____________________
☐ Category B
☐ Category C
☐ Category D
☐ Category E
Students
(Please modify where necessary)
The assignment must be typed in an MS Word document and submitted as a pdf via Learning Mall Online to the correct dropbox. Only electronic submission is accepted and no hard copy submission.
All students must download their file and check that it is viewable after submission. Documents may become corrupted during the uploading process (e.g. due to slow internet connections). However, students themselves are responsible for submitting a functional and correct file for assessments.
Assessment 001: Coursework
Due: May 17th, 2023 @ 17:00
Weight: 60%
Maximum Marks: 100
The coursework will be assessed for the following learning outcomes:
A.Demonstrate a basic understanding of the design of databases.
B.Show a fundamental grounding in the operation and usage of database management systems including "hands-on" experience of a basic database management system.
C.Demonstrate in-depth knowledge of the database language, SQL.
D.Show understanding of the legal processes and implications of creating and maintaining information systems.
Overview:
The purpose of this coursework is to design and implement a relational database system to solve a business information need. Working individually you will choose a scenario based on a real-world business example where a database would be needed and work your way through the conceptual, logical, and physical designs of a DBMS solution. This will require substantial research of best practices in design and the legal and ethical standards to which you must adhere during design. The skills required in this assessment will be valuable in the role of a DBMS professional or an IT manager, as these individuals are often tasked with developing solutions to various organisational data problems while also adhering to legal, ethical, and financial considerations.
While the complexity of the selected business case will depend on many factors (including the type of organization, the scope of the identified challenge, and operational requirement differences), it should sufficiently reflect the fundamental organizational operating rules.
There are the following parts of this coursework.
1.Decide on a topic, describe the need for the database, and identify business requirements. (15%)
2.Conceptual design using Entity-Relationship Diagram (ERD) (20%)
3.Logical design using the relational model (15%)
4.Physical design (Create tables, insert sample data into the created tables). (15%)
5.SQL queries that can be run against the database (20%)
6.Relational algebra queries that can be run against the database (15%)
7.Extra question: Web-based DBMS backend application (0%: does not count for final grade)
You will be required to write a brief report for each component and note down your process, thoughts, and assumptions made. You will also need to construct an APEX database and produce a set of queries that can be run against that database.
You are required to provide an APEX username, workspace, and password to access your queries and application for grading purposes.
Marking Criteria
The coursework will be graded out of 100 marks with a 60% weightage of the final grade.
Please see the assessment rubric at the end of this document outlining the criteria for assessment.
Your final report should be a complete, polished artefact that incorporates all the necessary detail from each of the components. This is an opportunity for you to pull all of your work from the term together into one complete project.
Your report should have the following sections:
Q1: Requirement Description (15%)
You will begin the project by choosing a topic. You will then identify your "client's" business requirements by doing some research.
Identify the business requirements that will allow you to understand the business processes. Build a list of business needs, rules and assumptions based on your scenario. Use the following categories to help you with this:
Business Scenario: You should clearly state the need for a database and identify its components in paragraphs. Usually, one paragraph pertains to one or more tables and relationships.
Business rules: It is used to understand business processes and the nature, role, and scope of the data.
Assumptions: It can be defined as a fact or a statement that has been taken for granted.
Problems: It can be defined as a situation or scenario that requires attention and a possible solution to alleviate the situation.
Project Ideas for your reference
The following list contains starting points for possible database systems. If you really think about it, any topic is a database waiting to happen. For additional ideas for your projects, flip through one of your favourite magazines or search online.
Project Ideas: Business
Collecting data for:
Restaurant orders and point of sale system
Hotel/flight/train reservation
Package delivery
Operations of a real-estate office
Automobile company
Auto-body repair
Health insurance
Amusement Park
Pet store
Organic farmer
Egg farmer
Limousine service
Project Ideas: Science
Collecting data on:
Health: current world epidemics, statistics, and locations, people affected, origins, causes, effects
Energy: gas resources, geothermal, solar, wind −Nuclear power plants −Radio telescopes
Household chemicals: disposal
Project Ideas: Historical
Building a presidential archive: −letters, speeches, books
Exploring and categorizing information about a historical event/place for tourists or researchers: for example, the Great Wall of China
There’s definitely a database needed around something that interests you. Start from here and do some research to come up with an idea. Whatever topic you choose, read articles, and check the internet to better understand your topic.
Here is a sample description of a hypothetical automobile company for your reference.
Business Scenario:
The application is an automobile company, such as General Motors, Ford, Toyota, etc. In our hypothetical company, it has been decided to redesign a major part of the database that underlies company operations. The company needs to keep quite a bit of data, but we shall focus on the following aspects of corporate operations.
Vehicles: Each vehicle has a vehicle identification number (VIN). Lots of stuff is encoded in real VINs (they are well described on Wikipedia), but you can just make them up if you want.
Brands: Each company may have several brands (for example, GM has Chevrolet, Pontiac, Buick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and Volkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT)
Models: Each brand offers several models (for example, Buick’s models are the Enclave, LaCrosse, and Lucerne, and Mercury’s models are the Mariner, Milan, Sable, and Grand Marquis). Each model may come in a variety of body styles (4-door, wagon, etc.)
Options: We’ll stick to colour, and maybe engine and transmission.
Dealers and customers: dealers buy vehicles from the manufacturer and sell them to customers. We’ll keep track of sales by date, brand, model, and colour; and also by the dealer. Note that a dealer may not sell some of the car company’s brands. Dealers keep some cars in inventory. Some, of course, are already sold, but the dealer still keeps track of that fact.
Suppliers: suppliers supply certain parts for certain models
company-owned manufacturing plants: Some plants supply certain parts for certain models; others do final assembly of actual cars.
Customers: In reality, lots of demographic data are gathered. We’ll stick to name, address, phone, gender, and annual income for individual buyers. The customer may also be a company (e.g. Hertz, Avis, or other companies that maintain corporate fleets, but we’ll skip that).
Q2: Conceptual Model (20%)
Devise a conceptual model using an Entity Relationship Diagram (ERD) that will best address the scenario you selected for the project. Your model should include all necessary entities, relationships, attributes, and business rules. Create a list of assumptions if applicable. The model should be well structured and organized for easy interpretation.
Explain why the selected design is suitable and if possible what are the other possible designs and why they are not chosen for the final design.
Q3: Logical Model (15%)
Based on the conceptual model, illustrate a normalized logical model for your DBMS that accurately represents all necessary aspects of the DBMS to address the solution. Use a table instance chart to map ERD into a relational model. The table diagram helps you map out a table before creating it in the database. You should describe the design of each table, by completing the table instance chart for each table mapped.
Your tables’ design should correspond to your ERD and must be in the third normal form (3NF). State candidate keys and functional dependencies of each table. Explain any assumptions you make applying what you know of the domain to the data and consider future data and the impact it may have as well. You will need to think and determine whether values are 'blank' (a known value of blank) or null (an as yet unknown value) as this may have an impact on your dependencies. Explain any assumptions and decisions you make in the report. If applicable, explain other possible designs and why they are not chosen for the final design.
Here is the sample table instance chart for your reference.
Q4: Physical Model (15%)
Create a physical database design that builds on the conceptual and logical models you crafted.
4a) Write the SQL DDL statements to create the fully normalized database. The SQL should contain CREATE statements for each table. You should include constraints and foreign keys where appropriate, and list and justify these in your report. Be sure to include appropriate constraints.
4b) Write INSERT statements to populate the new tables. Insert enough records (at least 10 rows in each table) so that you can run interesting and nontrivial queries on your database.
You have to consider the order of the tables when populating them. A table that has a foreign key field cannot be populated before the related table with the primary key
The SQL statements to create the tables and insert records should be saved as DDL. SQL and data.SQL and submit your scripts along with your report.
Q5: SQL Queries (20%)
In this task, you should write five different queries to showcase different SQL skills. State the purposes of each query in English, write SQL SELECT statements and provide a screenshot of the result to prove that the query works.
Challenging queries would amount to higher marks. The complexity of a query will be measured by the number of joins, select conditions and Group By clause, where a select condition counts as a ‘1’ and a join count as a ‘2’ and Group By condition count as ‘3’ (so, a SQL query that probably involved two select conditions, one join condition, and Group By clause will be counted as the complexity of ‘4’).
Below are the examples of queries statement in English for our hypothetical automobile company for your reference.
1)Show sales trends for various brands over the past 3 years, by year, month, and week. Then break these data out by gender of the buyer and then by income range.
2)Suppose that it is found that transmissions made by supplier Getrag between two given dates are defective. Find the VIN of each car containing such transmission and the customer to which it was sold. Suppose the defective transmissions all come from only one of Getrag’s plants if your design allows.
3)Find the top 2 brands by dollar amount sold in the past year.
4)Find the top 2 brands by unit sales in the past year.
5)Find those dealers who keep a vehicle in inventory for the longest average time.
6)In what month(s) do convertibles sell best?
Q6: Relational Algebra Queries (15%)
Write down five relational algebra queries that can be evaluated with your schema definitions. You can either write equivalent relational algebra queries of Q5 or write different queries. In any case, you should state the English specification of the query, as well as state the query in relational algebra expression.
The complexity of a query will be measured by the number of joins, select conditions and aggregate function, where a select condition counts as a ‘1’ and a join count as a ‘2’ and an aggregate function count as ‘3’ (so, a relational algebra expression that probably involved two select conditions, one join condition, and an aggregate function will be counted as the complexity of ‘4’).
Extra Question: Application Development/Interface Design (0%: Does Not Count Towards Final Grade)
In a database application, several types of users access the database. Each may need a special application:
For example, in our automobile company, the vehicle locator service needs a lookup application to check inventory both locally and at nearby dealers. This service allows a dealer to find a vehicle matching the desires of a potential customer. Marketing may want to review these inquiries to do future product planning.
Online customers need an elegant Web interface to find dealers and check products, inventories, and prices.
The marketing department needs sales reports and may want to do a special analysis.
These interfaces can be built using the Application builder component of Oracle APEX.
You are required to develop an application that will be used by users to access data and perform certain business operations. The application should have the following minimum interfaces:
1.Every table should have a main FORM where we can perform the following tasks:
a.Add new data to the table using the form. Apply validation on each field (e.g. if the Primary key is duplicate, It should display an error message)
b.Delete/ update record from each table
c.If a record that refers to another table is deleted or updated apply CASCADE Update and CASCADE Delete (Primary foreign key relationship)
2.Implement business functions for different users (if applicable).
3.Implement interactive reports where the user should be able to select a few parameters and the system should provide a result summary.
In the report provide a screenshot of each interface with a label and briefly describe what functionality it is providing. The description shall serve the purpose of the user manual.
Report Submission Guidelines
You should submit a single final report with the script files at LMO. Everything else should be packed into a single zip file. You should submit the following:
1.A document called [‘your student Id’].pdf, which is your final report.
2.You should submit the following three scripts files
1)Create table statements saved as ddl.sql
2)Insert statements saved as data.sql
3)Query statements saved as QueryScript.SQL
Generic Marking Criteria and Assessment Rubric
The below are generic marking criteria and assessment rubric.
Support
Any questions and answers will be added to the FAQ. Please use the coursework discussion channel at LMO as the first point of call for any questions, problems, clarifications, or anything you would like us to go over. If you prefer to ask privately, please send an email to the relevant instructor.
Good Luck!
Generic Marking Criteria
Grade Point Scale Criteria to be satisfied
A 81+ First Outstanding work that is at the upper limit of performance.
Work would be worthy of dissemination under appropriate conditions.
Mastery of advanced methods and techniques at a level beyond that explicitly taught.
Ability to synthesise and employ in an original way ideas from across the subject.
In group work, there is evidence of an outstanding individual contribution.
Excellent presentation.
Outstanding command of critical analysis and judgment.
B 70 - 80 First Excellent range and depth of attainment of intended learning outcomes.
Mastery of a wide range of methods and techniques.
Evidence of study and originality clearly beyond the bounds of what has been taught.
In group work, there is evidence of an excellent individual contribution.
Excellent presentation.
Able to display a command of critical thinking, analysis and judgment.
C 60 - 69 Upper Second Attained all the intended learning outcomes for a module or assessment.
Able to use well a range of methods and techniques to come to conclusions.
Evidence of study, comprehension, and synthesis beyond the bounds of what has been explicitly taught.
Very good presentation of material.
Able to employ critical analysis and judgement.
Where group work is involved there is evidence of a productive individual contribution
D 50- 59 Lower Second Some limitations in attainment of learning objectives but has managed to grasp most of them.
Able to use most of the methods and techniques taught.
Evidence of study and comprehension of what has been taught
Adequate presentation of material.
Some grasp of issues and concepts underlying the techniques and material taught.
Where group work is involved there is evidence of a positive individual contribution.
E 40 - 49 Third Limited attainment of intended learning outcomes.
Able to use a proportion of the basic methods and techniques taught.
Evidence of study and comprehension of what has been taught, but grasp insecure.
Poorly presented.
Some grasp of the issues and concepts underlying the techniques and material taught, but weak and incomplete.
F 0 - 39 Fail Attainment of only a minority of the learning outcomes.
Able to demonstrate a clear but limited use of some of the basic methods and techniques taught.
Weak and incomplete grasp of what has been taught.
Deficient understanding of the issues and concepts underlying the techniques and material taught.
Attainment of nearly all the intended learning outcomes deficient.
Lack of ability to use at all or the right methods and techniques taught.
Inadequately and incoherently presented.
Wholly deficient grasp of what has been taught.
Lack of understanding of the issues and concepts underlying the techniques and material taught.
Incoherence in presentation of information that hinders understanding.
G 0 Fail No significant assessable material, absent, or assessment missing a "must pass" component.
Assessment Rubric
Category 4 3 2 1 0 Points Weight Marks Received =Points*weight
Case Description (15%) Analyzes the selected case to highlight the database need and stated business requirements with necessary detail Analyzes the selected case to highlight the database need and business requirements, but lacks some detail Highlight the database need and business requirements, but with the gaps inaccuracy or detail Highlight the database need and business requirements to some extent with gaps inaccuracy or detail Database needs and business requirements were not identified 3.75
Conceptual Model (20%) 90-100% of entities, attributes, and relationships with optionality and cardinality are identified. Assumptions made in creating the ERD were clearly explained and consistent with the case study 80-89% of entities, attributes, and relationships with optionality and cardinality are identified. Assumptions made in creating the ERD were clearly explained and supported by the case study 70-78% of entities, attributes, and relationships with optionality and cardinality are identified.
Assumptions made in creating the ERD were clearly explained and supported by the case study Devises an ERD that does not attend to necessary entities, relationships, attributes, and business rules or would not logically address the identified problem ERD was not created 5
Logical Model (15%) All tables were mapped reflecting the ERD, were in 3rd Normal form and all necessary constraints were identified All tables were mapped reflecting the ERD 80-89% of tables were in 3rd Normal form and necessary constraints were identified 0ne or two tables were missing, 70-78% of tables were in 3rd Normal form and necessary constraints were identified More than two tables were missing, a few tables were not normalized, and necessary constraints were missing Tables were either missing or unnormalized 3.75
Physical Design (15%) Created all tables using correct DDL SQL statements
Completely populated tables with correct data elements reflecting the design of the tables Created most of the tables properly using correct DDL SQL statements.
Populated tables with the majority of data elements outlined in the tables’ design Created tables properly using DDL SQL statements with some errors.
Populated tables with some data elements defined in tables’ design Created tables with significant errors.
Populated tables with minimal data elements defined in tables’ design The database was not created 3.75
SQL (20%) Consistency between the English specification of the query, and the SQL implementation Demonstrated mastery in SQL skills by 4-5 complex queries Consistency between the English specification of the query, and the SQL implementation
Adequately demonstrated SQL skills by creating 2-3 complex queries using joins and data filter options Some inconsistencies between the English specification of the query, and the SQL implementation
Adequately
Created 1-2 complex queries by using different data filter options and joins Inconsistency between the English specification of the query, created only basic queries to retrieve data from a single table. The use of data filter and joins were not evident Relevant queries were not created 5
Relational Algebra (15%) Consistency between the English specification of the query, and the relational algebra implementation created 4-5 complex queries Consistency between the English specification of the query, and the relational algebra implementation created 2-3 complex queries Some inconsistency between the English specification of the query, and the relational algebra implementation created 1 complex query Inconsistency between the English specification of the query, created only basic, use of data filter and joins were not evident Relevant queries were not created 3.75
Application Development (0%: does not count towards final grade) Implemented all the business functionalities and created forms and reports that accurately reflect fundamental organizational business operations Correctly implemented most of the business functionalities and created all necessary forms and reports to support fundamental business operations Partially implemented business functionalities, forms, and reports Few basic functionalities were implemented Business functionalities were not implemented 0
Total Marks = 100
+