DTS106TC: Introduction to Database

Due: May 27^th, 2024 @ 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%)

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 and contributes 60% of the overall credit for the module. 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.

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 and assumptions: It is used to understand business processes and the nature, role, and scope of the data.

Problems and possible solutions: It can be defined as legal, ethical, and financial considerations that requires attention and a possible solution to alleviate the situation.

Project Ideas and Hints 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

· Auto-body repair

· Health insurance

· Amusement Park

· Pet store

· …

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

· …

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.

Business rules and assumptions:

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)

…

Problems and possible solution:

Legal Considerations & solution: …

Ethical Considerations & solution:  …

Financial Considerations & solution: …

Q2: Conceptual Model (20%)

Step 1: Using text analysis on the given scenario identify entities and attributes that will be used to store information about identified entities. Entities are usually the nouns in the scenario, and Attributes are normally found by identifying nouns that describe other nouns [5 marks].

Step 2: Identify relationships using business rules , write the optionality of the relationship between both entities (remember the relationship exists in both directions) with justification. Determine the cardinality by analyzing the description given for each relationship. If the description uses “a” or “an” then it should be a 1 relation (single toe) however if it says “many” or “one or more” then it should be a M relation (crow’s foot). Add the cardinality to the following entities by adding any required crow’s foot notation [5 marks].

Step 3: 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 with justification. Create a list of assumptions if applicable. The model should be well structured and organized for easy interpretation [8 marks].

Step 4: 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 [2 marks].

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.

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 [10 marks].

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 [5 marks].

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 and explanation of each query in English, and 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’).

Q6: Relational Algebra Queries (15%)

Write down five relational algebra queries and corresponding explanation 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’).

Report Submission Guidelines

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. Only electronic submission is accepted and no hard copy submission.

You should submit  single final report (PDF) and a zip script file at LMO. You should submit the following:

1. A document named [‘your student name_Id’].pdf, which is your final report. The assignment must be typed in an MS Word document and submitted as a pdf via Learning Mall.

2. You should submit a zip file including the relevant scripts files, e.g.,

1) Create table statements saved as ddl.sql

2) Insert statements saved as data.sql

3) Query statements saved as QueryScript.SQL

4) ...

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.