Database Creation and Queries Submission

The assignment is to create a database based on an ERD, populate it with at least 10 records per table, and write SQL queries that support the needs of different stakeholders. The tasks include creating a database called UP654321db, writing 4 queries for the database that include selection, projection, and join, and writing 4 more advanced SQL queries that demonstrate understanding of ordering, aggregation functions, grouping, etc.

1

Submission 4 INDIVIDUAL: The Database (32%)
Deadline: Friday 31st March 2017 (week 24)
Submission: Via CAM (changed 23/03/17) - Submit an A4 printout which contains all of your
SQL for the database creation and your queries including screenshots (see below)
Note: Prior to beginning work on Submission 4 you are advised to look at the feedback you
obtained for submission 1-3 AND to look at the model ERD answer which will be issued 21 working
days after the deadline for submission 3. Then do either of the following:
1. Use the model ERD to implement your database (with or without improvements)
2. Use YOUR ERD to implement your database (with or without improvements)
Task 5: Database Creation
1) Within your VM, create a folder called INDADD cswk
(this is no longer required - changed 23/03/17)
2) In that folder create a database called UP654321db
3) Populate your database with relevant (where possible) or dummy data. For each table, at
least 10 records/tuples should be inserted, wherever possible. Create all of the tables you will
need (based on your ERD - see note above)
4) Your submission must include a screen capture of the SQL you use to create and populate
your database.
[8 Marks]
Guidance: Your database should be based around your ERD although it is fine to adapt/change it
following feedback or more thought. If in doubt, ask your tutor. You will need to consider things
such as good attribute names, using the correct data types, limiting the size or range of inputs,
declaring primary and foreign keys and any constraints. We will also consider the efficiency and
layout of your SQL
Marking Scheme:
Mark Criteria
7-8 Database successfully created with all relevant attribute names, keys, data types, limits
and constraints. Tables populated with 10+ records with relevant/dummy data. Excellent
code layout
5-6 Database successfully created with attribute names, keys, data types but with
wrong/missing limits and constraints. Tables populated with 7+ records with generally
relevant/dummy data. Good code layout
3-4 Database successfully created with errors in attribute names, keys, or data types and
missing most limits and constraints or perhaps most there but with errors. Tables
populated with generally relevant/dummy data.
1-2 Database attempted but doesn’t work or successfully created but not populated
0 Nil submission or major errors
2

Task 6: SQL Queries
Write 4 queries for your database that support the needs of the business.
● Your queries must be meaningful and include Selection, Projection and Join.
● Your queries must target at least 3 different stakeholders stakeholders and show a variety
of SQL commands.
For each query your submission must include:
● A short description of each statement (3 lines max - 1.5 line spacing).
● The query written in Relational Algebra
● The SQL query
● A screenshot of the command being run and the results output by your database.
[12 Marks]
Guidance: What we are looking for is; have you understood the business environment? Can you
produce meaningful queries which would produce information of use to stakeholders? For
example, a new customer might like to know, “what events have been run for over 50 people?” or,
“what events were the most attended in winter and in summer”. The queries will also be marked
according to their depth/quality so a basic query (example 1) will attract a lower mark than one
which shows a deeper level of understanding and has multiple levels (example 2).
Example 1 Example 2
SELECT eventName SELECT fName, lname, mobile
FROM Events FROM Staff
WHERE eventManager = “Fred Bloggs” WHERE eventName = “Uni Fun Run” &
Gender = “F” &
Role = “medical”
Do not use these examples in your coursework, you will get a mark of zero if you do
Marking Scheme:
For each query allocate marks for each of the following:
Criteria Mark Details
Business
need
/description
1 Clear business need for the information returned by the query
0.5 Weak business need for the information returned by the query
SQL 1 Complex, multi-level query
0.5 Simple query
RA 1 Correct
0.5 Good attempt but minor errors
Note: Marks will be reduced if your queries are repetitive
3

Task 7: SQL Advanced Queries
Write 4 more queries using more advanced SQL statements:
● Your queries must be meaningful and include things such as Ordering, Aggregation
functions, Grouping, etc.
● Your queries must target at least 3 different stakeholders and show a variety of SQL
commands.
For each query your submission must include:
● The SQL query
● A short description of each statement (3 lines max).
● A screenshot of the command being run and the results output by your database.
[12 Marks]
Guidance: Again, the queries will be marked according to their depth/quality so a basic query will
attract a lower mark than one which shows a deeper level of understanding and has multiple
levels.
Marking Scheme:
For each query allocate marks for each of the following:
Criteria Mark Details
Business
need
/description
1.5 Excellent business need showing depth of thought and an excellent
understanding of the sorts of information businesses need
1 Clear business need showing good understanding of the sorts of
information businesses need
0.5 Weak business need showing some understanding of the sorts of
information businesses need
SQL
1.5 Complex, multi-level query showing depth of SQL knowledge beyond
that taught on the unit
1 Complex, multi-level query
0.5 Simple query
Note: Marks will be reduced if your queries are repetitive
4

5