Database Design
Individual Assignment, Spring 2008.
You have been asked to create a database to hold the records for Communist Party
awards ceremonies.
The first set of data that you have been given is a list of the people who will be attending
the first ceremony. In this case, the ceremony is for members who joined the Party
during the 1970s. This list is incomplete, as the attendees’ names in Chinese characters
have not been supplied, but you know that they will be supplied later. The data has been
supplied in an Excel worksheet.
At the ceremony, many different awards will be presented. These include:
o Long Service Award: given to the 10 people who have been party members for the
longest time.
o Half the Sky Award: given to all female participants.
o First Joiner: given to the person who joined the Party at the youngest age.
Assignment:
1. Draw an Entity Relationship Diagram to describe this situation. You must
include this diagram as part of the assignment. [5 marks]
2. Create the appropriate tables in the database. You must include your SQL
commands for this in your assignment. [4 marks]
3. Clearly indicate how you used your ERD to plan the tables that you
created. [5 marks]
4. Load the data into the database. Provide the SQL. [ 5 marks]
5. List the names of everyone who has been given an award, and the
award(s) that they received. List the SQL commands you used to identify
them. [2 marks]
6. How many awards were given out in total? List the SQL commands you
used to find this value. [2 marks]
7. How many people joined the Party in each year? List their names for each
year, ordered by the month in which they joined. Provide your SQL. [2
marks]
8. How many people were born in August (of any year)? Provide your SQL.
[2 marks]
9. Create an extra database user who can enter data, but cannot edit or
delete data. Show the SQL you used for this. [2 marks]
10. Approximately how much disk space do you think this database will use?
[1 mark]
Total: 30 marks.
NOTE:
o Your SQL must have been tested, and should work when entered into the
database
o All search results must have meaningful column names.