Assignment
Consider the following tables (and data) that make up a database used to run Mafunzo University in a MySQL database environment.
|
Students
|
|
RegNo
|
Stu d Narne
|
DateOfBirth
|
Degree Course
|
|
J12/1234/2001
|
Ma ry Atieno
|
19901-1
|
BSc Computer Science
|
|
J25/9999/2001
|
Peter Ka ma u
|
1989-4-7
|
BSc Maths
|
|
P21/4554/2001
|
Mohammed Aziz
|
1991-6-7
|
BSc Chem
|
|
P21/4555/2001
|
J Noe Wa svpa
|
1991-3-4
|
BSc Chem
|
|
P21/4556/2001
|
Moses Ara ka
|
1990-5-17
|
BSc Chem
|
|
Z100/76786/2001
|
Joseph Musyoka
|
1990-4-4
|
BSc Maths, Chem
|
|
Courses
|
|
CourseCode
|
Title
|
Dept
|
|
SCH301
|
Physical Chem istry
|
Chemistry
|
|
SCH302
|
Inorpnic Chemistry
|
Chemistry
|
|
SCH303
|
Therm odyna mics
|
Chemistry
|
|
SCO206
|
Data has a System
|
CIT
|
|
SCO207
|
Computer Programming
|
CIT
|
|
SMA100
|
Basic Maths
|
Mathematics
|
|
SMA260
|
Linear Aleetra I
|
Mathematics
|
|
Registration
|
|
CourseCode
|
RegNo
|
TotalMark
|
|
SCH301
|
P21/4564/2001
|
62
|
|
SCH301
|
P21/4565/2001
|
71
|
|
SCH302
|
P21/4564/2001
|
41
|
|
SCH302
|
P21/4565/2001
|
72
|
|
SCH302
|
Z100/76786/2001
|
35
|
|
SCH303
|
J25/9999/2001
|
50
|
|
SCH303
|
P21/4565/2001
|
75
|
|
SCO206
|
J12/1234/2001
|
30
|
|
SCO206
|
J25/9959/2001
|
40
|
|
SCO207
|
J25/9999/2001
|
50
|
|
SMA200
|
J25/9999/2001
|
60
|
|
SMA200
|
Z100/76786/2001
|
70
|
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:
|
Stud_ID
|
Shame
|
Course_ID
|
Units
|
|
101
|
Lennon
|
MSI 250
|
3.00
|
|
101
|
Lennon
|
MSI 415
|
3.00
|
|
125
|
Johnson
|
MSI 331
|
3.00
|
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.