Final Part A

Final Part A
Students’ Name
Professors’ Name
Institution affiliation

Question 1
Tables
•Client (client code, client name, email, phone, interest, organization code).
•Attendance list (attendance code, event code, client code).
•Invitation list (invitation code, client code, event code).
•Events (event code, event name)
•Organization (organization code, organization name, organization type).

Entity relationship diagram (ERD)
4448175105410Invitation list
Invitation code
Client code
Event code
0Invitation list
Invitation code
Client code
Event code

4448175106045952500106045Client list
Client code
Client name
Email
Phone
Interest
Organization code
0Client list
Client code
Client name
Email
Phone
Interest
Organization code

2419350298452419350192405043053003937000241935018224500952500106045
444817550165Event
Event code
Event name
0Event
Event code
Event name

430530060325002438400165100002419350603250024193501651000444754031750
2428875174625002438400603250024193501746250359092517462500
180975038100016478253810001809750381000
17145008001000345757527051000952500203835Organization affiliation
Organization code
Organization name
Organization type
0Organization affiliation
Organization code
Organization name
Organization type

3105150103506Attendance list
Attendance code
Event code
Client code
0Attendance list
Attendance code
Event code
Client code

310515012827095250080645
Assumptions
The client can only attend one event at a time and that there exist clients who share the same names (Al-Masree, 2015).

Question 2
Neither the pet name nor the customer id in the pets table can replace the pet id as a primary key because there is the likelihood of having more than one pet sharing the same name and also a customer may have more than one pet.
Tables
•Office visits (visit date, visit time, pet id, visit room, visit treatment, visit charge, vet id).
•Pets (pet id, pet name, vet id, owner id, species, breed, color, date-of-birth).
•Owner (owner id, owner name, pet id, city, street, zip code, state, phone).
190500028505150018954752983865001685925297434000181927543554650032480254469765003248025435546500168592544697650045910504060190005199380357441500533400135744150045910504193540533400035744150045910504193540003143250346011500312420032886650031242003460115003124201138366503124200153606531242001536065002085340133794520859751052195Office visit
Visit date
Visit time
Pet id
Visit room
Visit-treatment visit charge
Vet id
0Office visit
Visit date
Visit time
Pet id
Visit room
Visit-treatment visit charge
Vet id
4781550650239Vet
Vet id
Vet name
Pet id
City
Street
Zip code
State
Phone
0Vet
Vet id
Vet name
Pet id
City
Street
Zip code
State
Phone
•Veterinarian (vet id, vet name, pet id, city, street, zip code, state, phone).
Entity relationship diagram
461962560134506470651283970647700967740Owner
Owner id
Owner name
Pet id
City
Street
Zip code
State
Phone
0Owner
Owner id
Owner name
Pet id
City
Street
Zip code
State
Phone
3552190167449535540951424305Pet
Pet id
Pet name
Vet id
Owner id
Species
Breed
Color
Date-of-birth
0Pet
Pet id
Pet name
Vet id
Owner id
Species
Breed
Color
Date-of-birth
47815504394200
Question 3
Tables
Employee (employee name, employee Id, phone).
Sales (sales code, item code, amount, employee Id).
Item (item code, item name).
Entity relationship diagram
174307529013150017430752996565009144001796415003419475306324000382905020250150032766003148965004019550187261500110490029965650011049001720215001875790255841518954752272665Item
Item code
Item name
0Item
Item code
Item name
32759654533903276600148590Sales
Sales code
Item code
Amount
Employee Id
0Sales
Sales code
Item code
Amount
Employee Id
495300177165Employee
Employee id
Employee name
Phone
0Employee
Employee id
Employee name
Phone
494665558165
Assumptions
The item can only be sold one and by one employee only. More so, the amount recorded in sales only reflects the selling price of the item (Al-Masree, 2015).
Question 4
Tables
Referee (referee Id, referee name, division code, phone, address).
Coach (coach Id, division code, contact, coach name).
Player (player Id, player name, division code, contact).
Division (division code, division name, area).

4667250242570Coach
Coach Id
Coach
Name
Division -code
Contact
00Coach
Coach Id
Coach
Name
Division -code
Contact
Entity relationship diagram
2733675-635Division
Division code
Division
Name
Area
00Division
Division code
Division
Name
Area
45148505994400045148505232400032194501561465003114675156146500309562524187150032194502418715001600200723265001600200808990002590800732790038671505518153219450156146500160020080899000373380059944000259080028759150025908002618740Referee
Referee Id
Referee name
Division code
Phone
Street
City
State
00Referee
Referee Id
Referee name
Division code
Phone
Street
City
State
46672502184400027336753422650064770059944000647701275590Player
Player Id
Player name
Division code contact
00Player
Player Id
Player name
Division code contact

Assumptions
A player only plays in only one division; every referee oversees only one division and that the coach can only train one team within one division (Al-Masree, 2015).
Question 5
Tables
Aircraft (plane code, size, model, manufacturer, description).
Maintenance (plane code, mechanic Id, repair type).
Mechanics (mechanic id, name, contact, payment rate).
Entity relationship diagram
5295901264731505438775264731535052001237615004686301139001520383511666240020383501570990094297627520900106680027520904686300130429003505200139001548291751237615004829175961390Mechanic
Mechanic id
Name
Contact
Payment rate
0Mechanic
Mechanic id
Name
Contact
Payment rate
4953001390015004953001047115Maintenance
Plane code
Mechanic Id
Repair type
0Maintenance
Plane code
Mechanic Id
Repair type
2181225399416Aircraft
Plane code
Size
Model
Manufacturer
Description
0Aircraft
Plane code
Size
Model
Manufacturer
Description
543877526473150010668002733040001066800323786500350520013900150157162516662400021812257327900
15716252901950
Assumptions
There are several planes and mechanic with similar names, thus necessitating the use of plane code and mechanic id respectively. More so, a mechanic can conduct maintenance on many planes and a plane can be repaired by many mechanics. More so, more than one maintenance activities can be done on the same airplane (Al-Masree, 2015).
Question 6
Tables
Customer (customer id, contact, name, condos code, pet-charges, date, time).
Owners (owner Id, contact, Name, condos code).
Condos (condos code, name, no-of-bedrooms, allows a pet, high-speed internet, type, location).
Transactions (transaction Id, customer id, date, time, amount).

Entity relationship diagram
490474014268450049053751274445001170940365569500413385013411200026574751198245002105025119824500128587636556950040005001417320001914525127444502847975236220Customer
Customer id
Contact
Name
Condos code
Pet-charges
Date
Time
00Customer
Customer id
Contact
Name
Condos code
Pet-charges
Date
Time
762000711835Condos
Condos code
Name
No-of-bedrooms
Allows pet
High-speed internet
Type
Location
00Condos
Condos code
Name
No-of-bedrooms
Allows pet
High-speed internet
Type
Location
7620001093470050387259982200284797557912005038725645795Transaction
Transaction Id
Customer id
Date
Time
Amount
0Transaction
Transaction Id
Customer id
Date
Time
Amount

12858754191000
809625161290Owner
Owner Id
Contact
Name
Condos code
00Owner
Owner Id
Contact
Name
Condos code
1238250123190
80962517526000

Assumptions
The housekeeping such as cleaning charges is deducted at the time the owner gets his share. The customer can make several transactions. Every condo is registered under one owner and one owner can own several condos. A condo can only belong to one registered owner (Al-Masree, 2015).

References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), 15-26.