Database Design & Development ASSIGNMENT ONE - Database Design & SQL

This assignment solution addresses core concepts in database design and development. It begins with designing a minimum database, creating an ER diagram, and generating a Global Relation Diagram (GRD). The solution includes writing SQL scripts to create and populate tables, along with screenshots of the data. The assignment then progresses to more complex SQL queries, including listing buyers by family name, item names and prices for a given order, and orders by number and buyer name. Further, it involves drawing an ER diagram from a table-linking diagram, creating and updating tables, and writing SQL statements to extract specific data based on various criteria such as client names, staff information, and property details. The final sections address database modeling through a case study and selected additional exercises, covering topics like SQL joins and relational algebra.

Database Design & Development
ASSIGNMENT ONE
Due 5 May 2018 (end of week 9) b
Group Work: (Both contributed equally)
Student Number: 19121345
Student Name: Hasibur Rahman
&
Student Number: 19506135
Student Name: Mamun Chowdhury

Q1. Basic database design and queries
1. Design a minimum database (a database of only essential attributes) to fulfil this purpose,
and draw the ER diagram for your design. Indicate on the ER diagram the primary keys and
the relationship multiplicities. By minimum we mean that you don't need to add anything
that is not explicitly stated in the requirements, unless it's one of your artificially generated
keys. For instance, you don't need to include a telephone number or email address for the
entity corresponding to a buyer. (1.5 marks)
2. Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above,
indicating all the primary keys and foreign keys. The GRD should be in a form similar to
Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but all the attributes should be
kept there too. (1 mark)

3. Write an SQL script (of statements) that generates all the tables for your designed database. (1
mark).

4. Write an SQL script to insert sufficient records into your tables. Each table should contain no
less than 3 records. At least 2 orders should each contain 2 or more items of the ordered
merchandise. Screenshots are required for the records of all the tables. (1 mark

5. List all all buyers whose name contains your own family name as a substring. Insert sufficient
records into your table/s so that your query returns at least 2 records – that is yours and your group
member’s family name. (screenshot required). (0.5 marks)

6 For a given order (number), write an SQL statement to list all the item names and their
corresponding prices for the order (screenshot required). (0.5 marks)

7. List all the orders by their order number, date, and the name of the buyer who places that order
(screenshot required). (0.5 marks)
Q2. More on SQL queries (3 marks)

1. Draw an ER diagram to represent the above table-linking
diagram (which is essentially what we would call a relation
diagram). The ER diagram should bear fewer entity types
than the number of tables in the above displayed diagram.
That is, the table or tables that essentially represent
relationships should be represented as relationships on the
ER diagram, not as entities. (0.5 marks)

2. Create this set of tables and fill the records by executing the
necessary SQL script for each of these tables. Use the script

below for the STAFF table as an example, but proceed to
complete the rest of the tables on your own:
3. Use the following script to enter these records into the STAFF table, then do a
screenshot to show the table containing these records (SELECT * FROM Staff;):

4. Then use an UPDATE statement in SQL to modify the staff member "Julie Lee" into your
own name and modify the date of birth (13/6/1965) into a date after 1990s. If you have
team member for this assignment, then also UPDATE the staff record for staffNo="SL21"
by replacing the name "John White" there by that of your team partner. (0.5 marks)

5. Write an SQL statement to list all the client names, the maximum rent they are willing to
pay, and their telephone numbers (screenshot required). (0.5 marks)
6. Write an SQL statement to list staff name, position, and the postcode of their branch.
The listing should be ordered according to postcode, and within the same postcode,
ordered alphabetically according to the last name (screenshot required). (0.5 marks)