Assignment

Consider the following tables (and data) that make up a database used to run Mafunzo University in a MySQL database environment.

Required:

1. Answer the following questions:

i) Draw an E-R Diagram that could have been used to come up with the above database.

ii) Describe three situations in the above database design where primary. keys have been used.

iii) Describe an example of a situation in the above database design where a foreign key has been used

iv) Describe three examples of constraints lapart from primary key -foreign key related constraints) that could be implemented as part of this database design,

2. Using the program named Notepad (or any other suitable text edito0 write out an 'SQL batch file' to:

i) Construct a database named rvlafunzo
ii) Construct the database tables structures above

3. Using the program named Notepad (or any other suitable text editor) write out an 'SQL batch file' to populate the tables created in question 2 so that they contain the records as shown in the sample database above_

Be creative and don't just copy the format of the previously provided batch files. Run the batch SQL files and then attempt the following questions:

4. Write out SQL queries that would perform the following tasks:

i) Retrieve the name of each student who registered in the course titled Data base Systems'

ii) Retrieve the title of the course along with the number DI students who registered in this course in order of the student registration number.

iii) Retrieve for each student the average mark attained for the courses registered for. Arrange the list in order of students' ages in starting from the oldest to the youngest

iv) Generate a list of course units (title and code) and respective average, standard deviation of mark attain ed by students taking the course

v) Generate a list of departments and the average mark attained by students taking courses offered in each department

5. Consider the following student relation:

Clearly showing the steps you have followed, normalize it to 2NF.

6. You have a consultancy in which you work parttime. A local branch of a Ikwetu bank has commissioned you to design a database for them. Your system should enable them to maintain information about customers and their accounts.

Draw an ER diagram to model the application given the following information:

- Each customer has a name, a permanent address, and a KFA Pin number.

• Each customer can have multiple phone numbers, and the same phone number may be shared by multiple customers_

• A customer can own multiple accounts, but each account is owned by a single customer.

• Each account has an account number, a type (such as saving, current: etc), and a balance:

• The bank issues an account statement for each account and mails it to its account owner every month_ As time goes on, there will be multiple statem ents of the same account

• Each statement has an issued date and a statem ent ID. All the statements of the same account have different statement IDs, but two different accounts could have statements with the sam e statement ID. For example, it is possible that account A has a statement with ID '123', while account I3 has another statem ent with the same ID '123'.

If you need ad ditional assumptions to complete your diagram, please state your assumptions here as well.