About this Assignment This is a Database Programming course. This course covers advanced topics in databases. It starts by reviewing basic knowledge on databases and ends with advanced database...

1 answer below ยป

About this Assignment


This is a Database Programming course. This course covers advanced topics in databases. It starts by reviewing basic knowledge on databases and ends with advanced database concepts like security.


In this project, you will use the knowledge you acquired throughout the course to build a simple database and query it to extract information from it. You will create tables and relationships among them, in addition to the necessary keys and indexes. The next step will be to populate the database with suitable data. Populating the tables with sufficient and appropriate example data is an important step in testing and validating your design. When your database is ready, you will write SQL queries to retrieve information.


Upon completion of this project, you will be able to:



  • Write SQL queries to create tables

  • Write SQL queries to create relationships among tables

  • Identify indexes and create them in a database

  • Write queries to extract important information from a database


Prompt


In this project you will build a database for a public library. This database is aimed to collect and analyze information about the clients' reading interests. The project concentrates only on books and the clients' interests in books. The analyses that will result from this project will be used by the library's management to decide on the future purchasing policy.


A. Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) of the database shown in Figure 1. For simplicity, we are assuming in this project that a book cannot be written by more than one author. You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables.


Figure 1: ERD for Library Database








ERD diagram


B. Populate your database with the sample set of data given to you in the tables below the assignment prompts.


C. Write the following queries to retrieve the information detailed below.



  1. Display all contents of the Clients table

  2. First names, last names, ages and occupations of all clients

  3. First and last names of clients that borrowed books in March 2018

  4. First and last names of the top 5 authors clients borrowed in 2017

  5. Least 5 author nationalities clients borrowed during the years 2015-2017

  6. The book that was most borrowed during the years 2015-2017

  7. Top borrowed genres for client born in years 1970-1980

  8. Top 5 occupations that borrowed the most in 2016

  9. Average number of borrowed books by job title

  10. Create a VIEW and display the titles that were borrowed by at least 20% of clients

  11. The top month of borrows in 2017

  12. Average number of borrows by age

  13. The oldest and the youngest clients of the library

  14. First and last names of authors that wrote books in more than one genre


As you work on these queries, create indexes that will increase your queries' performance.


You must include comments in your code that address the purpose of your query and explains each step. Save your queries and results in a plain-text file that you will submit as your assignment.


Author table:






















































































































































AuthorIdAuthorFirstNameAuthorLastNameAuthorNationality
1SofiaSmithCanada
2MariaBrownBrazil
3ElenaMartinMexico
4ZoeRoyFrance
5SebastianLavoieCanada
6DylanGarciaSpain
7IanCruzMexico
8LucasSmithUSA
9FabianWilsonUSA
10LiamTaylorCanada
11WilliamThomasGreat Britain
12LoganMooreCanada
13OliverMartinFrance
14AlyshaThompsonCanada
15IsabelleLeeCanada
16EmilyClarkUSA
17JohnYoungChina
18DavidWrightCanada
19ThomasScottCanada
20HelenaAdamsCanada
21SofiaCarterUSA
22LiamParkerCanada
23EmilyMurphyUSA

Book table:












































































































































































































BookIDBookTitleBookAuthorGenre
1Build your database system1Science
2The red wall2Fiction
3The perfect match3Fiction
4Digital Logic4Science
5How to be a great lawyer5Law
6Manage successful negotiations6Society
7Pollution today7Science
8A gray park2Fiction
9How to be rich in one year8Humor
10Their bright fate9Fiction
11Black lines10Fiction
12History of theater11Literature
13Electrical transformers12Science
14Build your big data system1Science
15Right and left13Children
16Programming using Python1Science
17Computer networks14Science
18Performance evaluation15Science
19Daily exercise16Well being
20The silver uniform17Fiction
21Industrial revolution18History
22Green nature19Well being
23Perfect football20Well being
24The chocolate love21Humor
25Director and leader22Society
26Play football every week20well being
27Maya the bee13Children
28Perfect rugby20Well being
29The end23Fiction
30Computer security1Science
31Participate22Society
32Positive figures3Fiction

Client table:





























































































































































































































































































































































































































































































































































































ClientIdClientFirstNameClientLastNameClientDoBOccupation
1KaidenHill2006Student
2AlinaMorton2010Student
3FaniaBrooks1983Food Scientist
4CourtneyJensen2006Student
5BrittanyHill1983Firefighter
6MaxRogers2005Student
7MargaretMcCarthy1981School Psychologist
8JulieMcCarthy1973Professor
9KenMcCarthy1974Securities Clerk
10BritanyO'Quinn1984Violinist
11ConnerGardner1998Licensed Massage Therapist
12MyaAustin1960Parquet Floor Layer
13ThierryRogers2004Student
14EloiseRogers1984Computer Security Manager
15GerardJackson1979Oil Exploration Engineer
16RandyDay1986Aircraft Electrician
17JodiePage1990Manufacturing Director
18CoralRice1996Window Washer
19AymanAustin2002Student
20JaxsonAustin1999Repair Worker
21JoelAustin1973Police Officer
22AlinaAustin2010Student
23ElinAustin1962Payroll Clerk
24OpheliaWolf2004Student
25EliotMcGuire1967Dentist
26PeterMcKinney1968Professor
27AnnabellaHenry1974Nurse
28AnastasiaBaker2001Student
29TylerBaker1984Police Officer
30LilianRoss1983Insurance Agent
31ThierryArnold1975Bus Driver
32AngelinaRowe1979Firefighter
33MarciaRowe1974Health Educator
34MartinRowe1976Ship Engineer
35AdelineRowe2005Student
36ColetteRowe1963Professor
37DianeClark1975Payroll Clerk
38CarolineClark1960Dentist
39DaltonClayton1982Police Officer
40SteveClayton1990Bus Driver
41MelanieClayton1987Computer Engineer
42AlanaWilson2007Student
43CarsonByrne1995Food Scientist
44ConradByrne2007Student
45RyanPorter2008Student
46ElinPorter1978Computer Programmer
47TylerHarvey2007Student
48AryaHarvey2008Student
49SerenaHarvey1978School Teacher
50LillyFranklin1976Doctor
51MaiFranklin1994Dentist
52JohnFranklin1999Firefighter
53JudyFranklin1995Firefighter
54KatyLloyd1992School Teacher
55TamaraAllen1963Ship Engineer
56MaximLyons1985Police Officer
57AllanLyons1983Computer Engineer
58MarcHarris1980School Teacher
59ElinYoung2009Student
60DianaYoung2008Student
61DianeYoung2006Student
62AlanaBird2003Student
63AnnaBecker1979Security Agent
64KatieGrant1977Manager
65JoanGrant2010Student
66BryanBell2001Student
67BelleMiller1970Professor
68PeggyStevens1990Bus Driver
69SteveWilliamson1975HR Clerk
70TylerWilliamson1999Doctor
71IzabelleWilliamson1990Systems Analyst
72AnnabelWilliamson1960Cashier
73MohamedWaters1966Insurance Agent
74MarionNewman1970Computer Programmer
75AdaWilliams1986Computer Programmer
76SeanScott1983Bus Driver
77FarrahScott1974Ship Engineer
78ChristineLambert1973School Teacher
79AlyshaLambert2007Student
80MaiaGrant1984School Teacher

Borrower table:




















































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































BorrowIdClientIdBookIdBorrowDate
1351720/07/2016
21319/04/2017
342803/10/2016
4621605/04/2016
5531317/01/2017
6331526/11/2015
7401421/01/2015
864210/09/2017
9563002/08/2017
1023228/06/2018
11461918/11/2015
12612024/11/2015
1358717/06/2017
14461612/02/2017
15802118/03/2018
16512301/09/2015
17491828/07/2015
18431804/11/2015
1930210/08/2018
20482413/05/2015
2171505/09/2016
2235303/07/2016
2357117/03/2015
24232516/08/2017
25201224/07/2018
2625731/01/2015
27722910/04/2016
28742031/07/2017
29531420/02/2016
30321024/07/2017
31121525/04/2018
32771309/06/2017
3330424/10/2017
34372414/01/2016
35272605/06/2017
3611606/05/2018
3721919/03/2016
38692829/03/2017
39171914/03/2017
408922/04/2016
41631825/01/2015
42652010/10/2016
43511928/07/2015
44231225/01/2017
4517418/04/2017
4668506/09/2016
47461330/09/2017
48151305/07/2017
49111914/12/2017
50781526/01/2017
5147903/03/2015
5268726/05/2016
53372606/02/2017
54482730/12/2015
5592121/10/2017
5629801/04/2018
57641829/08/2017
58612621/02/2018
59392826/07/2016
60731822/08/2018
61111317/01/2018
6245620/07/2016
63331318/03/2018
64101706/06/2016
65281817/02/2017
6651309/12/2016
6729218/09/2015
68283014/09/2017
69742012/12/2015
70152214/01/2015
7157820/08/2017
722518/01/2015
73741214/04/2018
74511025/02/2016
75251724/02/2015
76452110/02/2017
77272503/08/2016
78322815/06/2016
79712121/05/2017
80752603/05/2016
81563223/12/2015
82263216/05/2015
83663230/05/2015
84571815/09/2017
85401502/09/2016
8665417/08/2017
8754719/12/2015
8829422/07/2017
8944931/12/2017
90563113/06/2015
9117401/04/2015
92351619/07/2018
93221822/06/2017
94392429/05/2015
95631420/01/2018
96532131/07/2016
9740910/07/2016
9852405/04/2017
99272004/09/2016
100722906/12/2015
101491619/12/2017
10261204/12/2016
103743127/07/2016
104483229/06/2016
10569227/12/2016
106603229/10/2017
107452212/06/2017
108421514/05/2017
10979813/10/2016
110701804/12/2016
11134806/03/2016
11243819/12/2015
113423220/04/2016
11467506/03/2017
115802523/06/2015
116541103/05/2017
117342830/08/2017
118652026/08/2017
119611905/01/2018
120381217/01/2018
12151413/05/2016
12271617/03/2016
123461625/11/2016
124753012/08/2018
125723212/03/2015
126441715/06/2015
127681521/02/2016
12821119/06/2016
129142510/10/2016
130682127/05/2016
131352019/03/2015
132162708/08/2016
133793107/03/2018
134141728/04/2018
135292811/03/2018
13641408/08/2018
13742323/02/2016
13845310/07/2017
139361619/07/2018
140363007/08/2015
141543214/03/2018
142611528/03/2017
14311317/05/2018
14443114/05/2015
145371430/07/2015
146621719/09/2015
147502202/12/2016
14845124/07/2016
149321710/03/2018
150132814/02/2016
15115911/08/2018
152101929/08/2018
15366327/11/2016
154682912/07/2017
155211427/06/2018
15635922/01/2016
157172425/08/2016
158402109/07/2015
15912428/03/2016
160702710/07/2015
161802624/04/2016
16229518/10/2015
163761225/04/2018
16422424/12/2016
1652226/10/2017
166351328/02/2016
16740802/10/2017
16868903/01/2016
16932513/11/2016
170341715/09/2016
171341613/04/2018
172803013/10/2016
173203217/11/2015
174361001/09/2017
175781227/06/2018
17657822/03/2016
177751127/06/2017
178711001/08/2015
179482229/09/2015
180191621/02/2016
181793020/08/2018
182701316/09/2016
18330610/02/2017
184451212/10/2017
185302723/11/2016
18626313/08/2016
18766614/01/2017
188471510/02/2016
189533008/08/2018
190801631/03/2016
191701303/02/2018
192142527/03/2016
193462213/01/2016
194303206/08/2015
195601427/11/2016
196141323/05/2018
197711522/06/2016
198382127/12/2015
199693029/04/2017
200493103/06/2018
201282829/05/2015
20249330/08/2016
20375129/10/2015
20478312/05/2017
205431825/03/2015
206272122/02/2016
207642203/04/2015
208211109/12/2017
209662920/12/2016
210451315/04/2017
211483031/01/2015
212202520/12/2017
213412029/01/2018
214511205/07/2015
2155112/04/2015
21640324/02/2018
21779427/06/2018
218151001/11/2016
219422228/12/2016
22017929/01/2018
221381309/05/2016
22279206/12/2017
22374307/12/2015
22446805/06/2016
225782211/08/2018
22645220/04/2015
227723111/11/2015
228181721/03/2015
22929313/08/2017
230661105/06/2018
231361628/04/2016
23226223/10/2016
23332131/10/2017
234621425/07/2017
23512408/07/2015
236383224/02/2015
237291628/07/2016
238362507/05/2017
23976713/06/2015
240281615/08/2016
241601326/08/2016
2428328/07/2017
24325130/07/2016
244622924/08/2018
24551801/09/2016
246272308/02/2015
247691225/06/2018
248511204/07/2015
2497401/05/2015
250311529/10/2017
251142315/01/2015
25214121/05/2018
253392526/12/2015
254792431/05/2016
255401518/03/2016
256511313/04/2018
25761111/02/2015
258152402/03/2018
259102221/01/2018
260671008/07/2017
261791111/12/2016
262193204/05/2016
263351101/08/2017
264271315/12/2017
265302222/12/2015
2668726/06/2015
26770920/03/2016
268561829/01/2016
269131906/03/2015
27061218/06/2016
271471318/09/2017
272302219/02/2016
273182231/12/2016
274342927/10/2017
275322103/06/2015
27692830/03/2016
277622423/03/2015
278442229/04/2017
27927525/03/2015
280612814/07/2017
28151304/12/2016
282431915/03/2018
283341905/06/2016
28435519/02/2018
285131223/09/2016
286741826/12/2016
287703115/08/2017
288421715/06/2016
289512430/07/2018
290453015/01/2015
291701707/10/2017
29277706/01/2017
293742525/09/2015
294471401/02/2018
29510218/04/2017
296162103/10/2016
29748517/09/2016
29872310/02/2017
299262301/03/2016
300492325/10/2016

Grading Rubric


Your project will be graded based on the following rubric:






















































































































































































CategoryUnacceptable (0-1)Needs Improvement (2-3)Good (4)Excellent (5)Total Possible Points
Create the database tablesAll tables are not created correctly2 or more tables are not created correctlyCorrect created tables, queries are not optimalCorrect queries and correct resulted tables5
Define the primary keys of the tablesAll primary keys are not created correctly2 or more primary keys are not created correctlyNAAll primary keys are created correctly5
Create the relationships among the tables using foreign keysAll relationships are not created correctly2 or more relationships are not created correctlyNAAll relationships are created correctly5
Populate the tables with dataTables are not populated by the given dataAll tables are populated by part of the given dataSome tables are populated by part of the given dataAll tables are correctly populated with the given data5
Query 1Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 2Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 3Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 4Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 5Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 6Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 7Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 8Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 9Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 10Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 11Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 12Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 13Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Query 14Query does not existQuery is close to correct, results are not correctCorrect results, query is not optimalCorrect query and correct results. Notes written to explain query.5
Create the required indexesNo indexes chosenIndexes are poorly chosen, queries are not correctIndexes are poorly chosen, queries are correctIndexes are correctly chosen, queries are correctly written5
Create required viewNo view createdThe syntax of the view creation is not correctThe view is correctly created but not correctly usedThe view is correctly created and used5
Total100
Answered 13 days AfterOct 27, 2021

Answer To: About this Assignment This is a Database Programming course. This course covers advanced topics in...

Dinesh answered on Nov 09 2021
95 Votes
doc/ert-xsm5c5ap.docx
About this Assignment
This is a Database Programming course. This course covers advanced topics in databases. It starts by reviewing basic knowledge on databases and ends with advanced database concepts like security.
In this project, you will use the knowledge you acquired throughout the course to build a simple database and query it to extract information from it. You will create tables and relationships among them, in addition to the necessary keys and indexes. The next step will be to populate the database with suitable data. Populating the tables with sufficient and appropriate example data is an important step in testing and validating your design. When your database is ready, you will write SQL queries to retrieve information.
Upon completion of this project, you will be able to:
ยท Write SQL queries to create tables
ยท Write SQL queries to create relationships among tables
ยท Identify indexes and create them in a database
ยท Write queries to extract important information from a database
Prompt
In this project you will build a database for a public library. This database is aimed to collect and analyze information about the clients' reading interests. The project concentrates only on books and the clients' interests in books. The analyses that will result from this project will be used by the library's management to decide on the future purchasing policy.
A. Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) of the database shown in Figure 1. For simplicity, we are assuming in this project that a book cannot be written by more than one author. You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables.
Figure 1: ERD for Library Database
        
B. Populate your database with the sample set of data given to you in the tables below the assignment prompts.
C. Write the following queries to retrieve the information detailed below.
1. Display all contents of the Clients table
2. First names, last names, ages and occupations of all clients
3. First and last names of clients that borrowed books in March 2018
4. First and last names of the top 5 authors clients borr
owed in 2017
5. Least 5 author nationalities clients borrowed during the years 2015-2017
6. The book that was most borrowed during the years 2015-2017
7. Top borrowed genres for client born in years 1970-1980
8. Top 5 occupations that borrowed the most in 2016
9. Average number of borrowed books by job title
10. Create a VIEW and display the titles that were borrowed by at least 20% of clients
11. The top month of borrows in 2017
12. Average number of borrows by age
13. The oldest and the youngest clients of the library
14. First and last names of authors that wrote books in more than one genre
As you work on these queries, create indexes that will increase your queries' performance.
You must include comments in your code that address the purpose of your query and explains each step. Save your queries and results in a plain-text file that you will submit as your assignment.
Author table:
        AuthorId
        AuthorFirstName
        AuthorLastName
        AuthorNationality
        1
        Sofia
        Smith
        Canada
        2
        Maria
        Brown
        Brazil
        3
        Elena
        Martin
        Mexico
        4
        Zoe
        Roy
        France
        5
        Sebastian
        Lavoie
        Canada
        6
        Dylan
        Garcia
        Spain
        7
        Ian
        Cruz
        Mexico
        8
        Lucas
        Smith
        USA
        9
        Fabian
        Wilson
        USA
        10
        Liam
        Taylor
        Canada
        11
        William
        Thomas
        Great Britain
        12
        Logan
        Moore
        Canada
        13
        Oliver
        Martin
        France
        14
        Alysha
        Thompson
        Canada
        15
        Isabelle
        Lee
        Canada
        16
        Emily
        Clark
        USA
        17
        John
        Young
        China
        18
        David
        Wright
        Canada
        19
        Thomas
        Scott
        Canada
        20
        Helena
        Adams
        Canada
        21
        Sofia
        Carter
        USA
        22
        Liam
        Parker
        Canada
        23
        Emily
        Murphy
        USA
Book table:
        BookID
        BookTitle
        BookAuthor
        Genre
        1
        Build your database system
        1
        Science
        2
        The red wall
        2
        Fiction
        3
        The perfect match
        3
        Fiction
        4
        Digital Logic
        4
        Science
        5
        How to be a great lawyer
        5
        Law
        6
        Manage successful negotiations
        6
        Society
        7
        Pollution today
        7
        Science
        8
        A gray park
        2
        Fiction
        9
        How to be rich in one year
        8
        Humor
        10
        Their bright fate
        9
        Fiction
        11
        Black lines
        10
        Fiction
        12
        History of theater
        11
        Literature
        13
        Electrical transformers
        12
        Science
        14
        Build your big data system
        1
        Science
        15
        Right and left
        13
        Children
        16
        Programming using Python
        1
        Science
        17
        Computer networks
        14
        Science
        18
        Performance evaluation
        15
        Science
        19
        Daily exercise
        16
        Well being
        20
        The silver uniform
        17
        Fiction
        21
        Industrial revolution
        18
        History
        22
        Green nature
        19
        Well being
        23
        Perfect football
        20
        Well being
        24
        The chocolate love
        21
        Humor
        25
        Director and leader
        22
        Society
        26
        Play football every week
        20
        well being
        27
        Maya the bee
        13
        Children
        28
        Perfect rugby
        20
        Well being
        29
        The end
        23
        Fiction
        30
        Computer security
        1
        Science
        31
        Participate
        22
        Society
        32
        Positive figures
        3
        Fiction
Client table:
        ClientId
        ClientFirstName
        ClientLastName
        ClientDoB
        Occupation
        1
        Kaiden
        Hill
        2006
        Student
        2
        Alina
        Morton
        2010
        Student
        3
        Fania
        Brooks
        1983
        Food Scientist
        4
        Courtney
        Jensen
        2006
        Student
        5
        Brittany
        Hill
        1983
        Firefighter
        6
        Max
        Rogers
        2005
        Student
        7
        Margaret
        McCarthy
        1981
        School Psychologist
        8
        Julie
        McCarthy
        1973
        Professor
        9
        Ken
        McCarthy
        1974
        Securities Clerk
        10
        Britany
        O'Quinn
        1984
        Violinist
        11
        Conner
        Gardner
        1998
        Licensed Massage Therapist
        12
        Mya
        Austin
        1960
        Parquet Floor Layer
        13
        Thierry
        Rogers
        2004
        Student
        14
        Eloise
        Rogers
        1984
        Computer Security Manager
        15
        Gerard
        Jackson
        1979
        Oil Exploration Engineer
        16
        Randy
        Day
        1986
        Aircraft Electrician
        17
        Jodie
        Page
        1990
        Manufacturing Director
        18
        Coral
        Rice
        1996
        Window Washer
        19
        Ayman
        Austin
        2002
        Student
        20
        Jaxson
        Austin
        1999
        Repair Worker
        21
        Joel
        Austin
        1973
        Police Officer
        22
        Alina
        Austin
        2010
        Student
        23
        Elin
        Austin
        1962
        Payroll Clerk
        24
        Ophelia
        Wolf
        2004
        Student
        25
        Eliot
        McGuire
        1967
        Dentist
        26
        Peter
        McKinney
        1968
        Professor
        27
        Annabella
        Henry
        1974
        Nurse
        28
        Anastasia
        Baker
        2001
        Student
        29
        Tyler
        Baker
        1984
        Police Officer
        30
        Lilian
        Ross
        1983
        Insurance Agent
        31
        Thierry
        Arnold
        1975
        Bus Driver
        32
        Angelina
        Rowe
        1979
        Firefighter
        33
        Marcia
        Rowe
        1974
        Health Educator
        34
        Martin
        Rowe
        1976
        Ship Engineer
        35
        Adeline
        Rowe
        2005
        Student
        36
        Colette
        Rowe
        1963
        Professor
        37
        Diane
        Clark
        1975
        Payroll Clerk
        38
        Caroline
        Clark
        1960
        Dentist
        39
        Dalton
        Clayton
        1982
        Police Officer
        40
        Steve
        Clayton
        1990
        Bus Driver
        41
        Melanie
        Clayton
        1987
        Computer Engineer
        42
        Alana
        Wilson
        2007
        Student
        43
        Carson
        Byrne
        1995
        Food Scientist
        44
        Conrad
        Byrne
        2007
        Student
        45
        Ryan
        Porter
        2008
        Student
        46
        Elin
        Porter
        1978
        Computer Programmer
        47
        Tyler
        Harvey
        2007
        Student
        48
        Arya
        Harvey
        2008
        Student
        49
        Serena
        Harvey
        1978
        School Teacher
        50
        Lilly
        Franklin
        1976
        Doctor
        51
        Mai
        Franklin
        1994
        Dentist
        52
        John
        Franklin
        1999
        Firefighter
        53
        Judy
        Franklin
        1995
        Firefighter
        54
        Katy
        Lloyd
        1992
        School Teacher
        55
        Tamara
        Allen
        1963
        Ship Engineer
        56
        Maxim
        Lyons
        1985
        Police Officer
        57
        Allan
        Lyons
        1983
        Computer Engineer
        58
        Marc
        Harris
        1980
        School Teacher
        59
        Elin
        Young
        2009
        Student
        60
        Diana
        Young
        2008
        Student
        61
        Diane
        Young
        2006
        Student
        62
        Alana
        Bird
        2003
        Student
        63
        Anna
        Becker
        1979
        Security Agent
        64
        Katie
        Grant
        1977
        Manager
        65
        Joan
        Grant
        2010
        Student
        66
        Bryan
        Bell
        2001
        Student
        67
        Belle
        Miller
        1970
        Professor
        68
        Peggy
        Stevens
        1990
        Bus Driver
        69
        Steve
        Williamson
        1975
        HR Clerk
        70
        Tyler
        Williamson
        1999
        Doctor
        71
        Izabelle
        Williamson
        1990
        Systems Analyst
        72
        Annabel
        Williamson
        1960
        Cashier
        73
        Mohamed
        Waters
        1966
        Insurance Agent
        74
        Marion
        Newman
        1970
        Computer Programmer
        75
        Ada
        Williams
        1986
        Computer Programmer
        76
        Sean
        Scott
        1983
        Bus Driver
        77
        Farrah
        Scott
        1974
        Ship Engineer
        78
        Christine
        Lambert
        1973
        School Teacher
        79
        Alysha
        Lambert
        2007
        Student
        80
        Maia
        Grant
        1984
        School Teacher
Borrower table:
        BorrowId
        ClientId
        BookId
        BorrowDate
        1
        35
        17
        20/07/2016
        2
        1
        3
        19/04/2017
        3
        42
        8
        03/10/2016
        4
        62
        16
        05/04/2016
        5
        53
        13
        17/01/2017
        6
        33
        15
        26/11/2015
        7
        40
        14
        21/01/2015
        8
        64
        2
        10/09/2017
        9
        56
        30
        02/08/2017
        10
        23
        2
        28/06/2018
        11
        46
        19
        18/11/2015
        12
        61
        20
        24/11/2015
        13
        58
        7
        17/06/2017
        14
        46
        16
        12/02/2017
        15
        80
        21
        18/03/2018
        16
        51
        23
        01/09/2015
        17
        49
        18
        28/07/2015
        18
        43
        18
        04/11/2015
        19
        30
        2
        10/08/2018
        20
        48
        24
        13/05/2015
        21
        71
        5
        05/09/2016
        22
        35
        3
        03/07/2016
        23
        57
        1
        17/03/2015
        24
        23
        25
        16/08/2017
        25
        20
        12
        24/07/2018
        26
        25
        7
        31/01/2015
        27
        72
        29
        10/04/2016
        28
        74
        20
        31/07/2017
        29
        53
        14
        20/02/2016
        30
        32
        10
        24/07/2017
        31
        12
        15
        25/04/2018
        32
        77
        13
        09/06/2017
        33
        30
        4
        24/10/2017
        34
        37
        24
        14/01/2016
        35
        27
        26
        05/06/2017
        36
        1
        16
        06/05/2018
        37
        21
        9
        19/03/2016
        38
        69
        28
        29/03/2017
        39
        17
        19
        14/03/2017
        40
        8
        9
        22/04/2016
        41
        63
        18
        25/01/2015
        42
        65
        20
        10/10/2016
        43
        51
        19
        28/07/2015
        44
        23
        12
        25/01/2017
        45
        17
        4
        18/04/2017
        46
        68
        5
        06/09/2016
        47
        46
        13
        30/09/2017
        48
        15
        13
        05/07/2017
        49
        11
        19
        14/12/2017
        50
        78
        15
        26/01/2017
        51
        47
        9
        03/03/2015
        52
        68
        7
        26/05/2016
        53
        37
        26
        06/02/2017
        54
        48
        27
        30/12/2015
        55
        9
        21
        21/10/2017
        56
        29
        8
        01/04/2018
        57
        64
        18
        29/08/2017
        58
        61
        26
        21/02/2018
        59
        39
        28
        26/07/2016
        60
        73
        18
        22/08/2018
        61
        11
        13
        17/01/2018
        62
        45
        6
        20/07/2016
        63
        33
        13
        18/03/2018
        64
        10
        17
        06/06/2016
        65
        28
        18
        17/02/2017
        66
        51
        3
        09/12/2016
        67
        29
        2
        18/09/2015
        68
        28
        30
        14/09/2017
        69
        74
        20
        12/12/2015
        70
        15
        22
        14/01/2015
        71
        57
        8
        20/08/2017
        72
        2
        5
        18/01/2015
        73
        74
        12
        14/04/2018
        74
        51
        10
        25/02/2016
        75
        25
        17
        24/02/2015
        76
        45
        21
        10/02/2017
        77
        27
        25
        03/08/2016
        78
        32
        28
        15/06/2016
        79
        71
        21
        21/05/2017
        80
        75
        26
        03/05/2016
        81
        56
        32
        23/12/2015
        82
        26
        32
        16/05/2015
        83
        66
        32
        30/05/2015
        84
        57
        18
        15/09/2017
        85
        40
        15
        02/09/2016
        86
        65
        4
        17/08/2017
        87
        54
        7
        19/12/2015
        88
        29
        4
        22/07/2017
        89
        44
        9
        31/12/2017
        90
        56
        31
        13/06/2015
        91
        17
        4
        01/04/2015
        92
        35
        16
        19/07/2018
        93
        22
        18
        22/06/2017
        94
        39
        24
        29/05/2015
        95
        63
        14
        20/01/2018
        96
        53
        21
        31/07/2016
        97
        40
        9
        10/07/2016
        98
        52
        4
        05/04/2017
        99
        27
        20
        04/09/2016
        100
        72
        29
        06/12/2015
        101
        49
        16
        19/12/2017
        102
        6
        12
        04/12/2016
        103
        74
        31
        27/07/2016
        104
        48
        32
        29/06/2016
        105
        69
        2
        27/12/2016
        106
        60
        32
        29/10/2017
        107
        45
        22
        12/06/2017
        108
        42
        15
        14/05/2017
        109
        79
        8
        13/10/2016
        110
        70
        18
        04/12/2016
        111
        34
        8
        06/03/2016
        112
        43
        8
        19/12/2015
        113
        42
        32
        20/04/2016
        114
        67
        5
        06/03/2017
        115
        80
        25
        23/06/2015
        116
        54
        11
        03/05/2017
        117
        34
        28
        30/08/2017
        118
        65
        20
        26/08/2017
        119
        61
        19
        05/01/2018
        120
        38
        12
        17/01/2018
        121
        51
        4
        13/05/2016
        122
        7
        16
        17/03/2016
        123
        46
        16
        25/11/2016
        124
        75
        30
        12/08/2018
        125
        72
        32
        12/03/2015
        126
        44
        17
        15/06/2015
        127
        68
        15
        21/02/2016
        128
        21
        1
        19/06/2016
        129
        14
        25
        10/10/2016
        130
        68
        21
        27/05/2016
        131
        35
        20
        19/03/2015
        132
        16
        27
        08/08/2016
        133
        79
        31
        07/03/2018
        134
        14
        17
        28/04/2018
        135
        29
        28
        11/03/2018
        136
        41
        4
        08/08/2018
        137
        42
        3
        23/02/2016
        138
        45
        3
        10/07/2017
        139
        36
        16
        19/07/2018
        140
        36
        30
        07/08/2015
        141
        54
        32
        14/03/2018
        142
        61
        15
        28/03/2017
        143
        1
        13
        17/05/2018
        144
        43
        1
        14/05/2015
        145
        37
        14
        30/07/2015
        146
        62
        17
        19/09/2015
        147
        50
        22
        02/12/2016
        148
        45
        1
        24/07/2016
        149
        32
        17
        10/03/2018
        150
        13
        28
        14/02/2016
        151
        15
        9
        11/08/2018
        152
        10
        19
        29/08/2018
        153
        66
        3
        27/11/2016
        154
        68
        29
        12/07/2017
        155
        21
        14
        27/06/2018
        156
        35
        9
        22/01/2016
        157
        17
        24
        25/08/2016
        158
        40
        21
        09/07/2015
        159
        1
        24
        28/03/2016
        160
        70
        27
        10/07/2015
        161
        80
        26
        24/04/2016
        162
        29
        5
        18/10/2015
        163
        76
        12
        25/04/2018
        164
        22
        4
        24/12/2016
        165
        2
        2
        26/10/2017
        166
        35
        13
        28/02/2016
        167
        40
        8
        02/10/2017
        168
        68
        9
        03/01/2016
        169
        32
        5
        13/11/2016
        170
        34
        17
        15/09/2016
        171
        34
        16
        13/04/2018
        172
        80
        30
        13/10/2016
        173
        20
        32
        17/11/2015
        174
        36
        10
        01/09/2017
        175
        78
        12
        27/06/2018
        176
        57
        8
        22/03/2016
        177
        75
        11
        27/06/2017
        178
        71
        10
        01/08/2015
        179
        48
        22
        29/09/2015
        180
        19
        16
        21/02/2016
        181
        79
        30
        20/08/2018
        182
        70
        13
        16/09/2016
        183
        30
        6
        10/02/2017
        184
        45
        12
        12/10/2017
        185
        30
        27
        23/11/2016
        186
        26
        3
        13/08/2016
        187
        66
        6
        14/01/2017
        188
        47
        15
        10/02/2016
        189
        53
        30
        08/08/2018
        190
        80
        16
        31/03/2016
        191
        70
        13
        03/02/2018
        192
        14
        25
        27/03/2016
        193
        46
        22
        13/01/2016
        194
        30
        32
        06/08/2015
        195
        60
        14
        27/11/2016
        196
        14
        13
        23/05/2018
        197
        71
        15
        22/06/2016
        198
        38
        21
        27/12/2015
        199
        69
        30
        29/04/2017
        200
        49
        31
        03/06/2018
        201
        28
        28
        29/05/2015
        202
        49
        3
        30/08/2016
        203
        75
        1
        29/10/2015
        204
        78
        3
        12/05/2017
        205
        43
        18
        25/03/2015
        206
        27
        21
        22/02/2016
        207
        64
        22
        03/04/2015
        208
        21
        11
        09/12/2017
        209
        66
        29
        20/12/2016
        210
        45
        13
        15/04/2017
        211
        48
        30
        31/01/2015
        212
        20
        25
        20/12/2017
        213
        41
        20
        29/01/2018
        214
        51
        12
        05/07/2015
        215
        5
        1
        12/04/2015
        216
        40
        3
        24/02/2018
        217
        79
        4
        27/06/2018
        218
        15
        10
        01/11/2016
        219
        42
        22
        28/12/2016
        220
        17
        9
        29/01/2018
        221
        38
        13
        09/05/2016
        222
        79
        2
        06/12/2017
        223
        74
        3
        07/12/2015
        224
        46
        8
        05/06/2016
        225
        78
        22
        11/08/2018
        226
        45
        2
        20/04/2015
        227
        72
        31
        11/11/2015
        228
        18
        17
        21/03/2015
        229
        29
        3
        13/08/2017
        230
        66
        11
        05/06/2018
        231
        36
        16
        28/04/2016
        232
        26
        2
        23/10/2016
        233
        32
        1
        31/10/2017
        234
        62
        14
        25/07/2017
        235
        12
        4
        08/07/2015
        236
        38
        32
        24/02/2015
        237
        29
        16
        28/07/2016
        238
        36
        25
        07/05/2017
        239
        76
        7
        13/06/2015
        240
        28
        16
        15/08/2016
        241
        60
        13
        26/08/2016
        242
        8
        3
        28/07/2017
        243
        25
        1
        30/07/2016
        244
        62
        29
        24/08/2018
        245
        51
        8
        01/09/2016
        246
        27
        23
        08/02/2015
        247
        69
        12
        25/06/2018
        248
        51
        12
        04/07/2015
        249
        7
        4
        01/05/2015
        250
        31
        15
        29/10/2017
        251
        14
        23
        15/01/2015
        252
        14
        1
        21/05/2018
        253
        39
        25
        26/12/2015
        254
        79
        24
        31/05/2016
        255
        40
        15
        18/03/2016
        256
        51
        13
        13/04/2018
        257
        61
        1
        11/02/2015
        258
        15
        24
        02/03/2018
        259
        10
        22
        21/01/2018
        260
        67
        10
        08/07/2017
        261
        79
        11
        11/12/2016
        262
        19
        32
        04/05/2016
        263
        35
        11
        01/08/2017
        264
        27
        13
        15/12/2017
        265
        30
        22
        22/12/2015
        266
        8
        7
        26/06/2015
        267
        70
        9
        20/03/2016
        268
        56
        18
        29/01/2016
        269
        13
        19
        06/03/2015
        270
        61
        2
        18/06/2016
        271
        47
        13
        18/09/2017
        272
        30
        22
        19/02/2016
        273
        18
        22
        31/12/2016
        274
        34
        29
        27/10/2017
        275
        32
        21
        03/06/2015
        276
        9
        28
        30/03/2016
        277
        62
        24
        23/03/2015
        278
        44
        22
        29/04/2017
        279
        27
        5
        25/03/2015
        280
        61
        28
        14/07/2017
        281
        5
        13
        04/12/2016
        282
        43
        19
        15/03/2018
        283
        34
        19
        05/06/2016
        284
        35
        5
        19/02/2018
        285
        13
        12
        23/09/2016
        286
        74
        18
        26/12/2016
        287
        70
        31
        15/08/2017
        288
        42
        17
        15/06/2016
        289
        51
        24
        30/07/2018
        290
        45
        30
        15/01/2015
        291
        70
        17
        07/10/2017
        292
        77
        7
        06/01/2017
        293
        74
        25
        25/09/2015
        294
        47
        14
        01/02/2018
        295
        10
        2
        18/04/2017
        296
        16
        21
        03/10/2016
        297
        48
        5
        17/09/2016
        298
        72
        3
        10/02/2017
        299
        26
        23
        01/03/2016
        300
        49
        23
        25/10/2016
Grading Rubric
Your project will be graded based on the following rubric:
        Category
        Unacceptable (0-1)
        Needs Improvement (2-3)
        Good (4)
        Excellent (5)
        Total Possible Points
        Create the database tables
        All tables are not created correctly
        2 or more tables are not created correctly
        Correct created tables, queries are not optimal
        Correct queries and correct resulted tables
        5
        Define the primary keys of the tables
        All primary keys are not created correctly
        2 or more primary keys are not created correctly
        NA
        All primary keys are created correctly
        5
        Create the relationships among the tables using foreign keys
        All relationships are not created correctly
        2 or more relationships are not created correctly
        NA
        All relationships are created correctly
        5
        Populate the tables with data
        Tables are not populated by the given data
        All tables are populated by part of the given data
        Some tables are populated by part of the given data
        All tables are correctly populated with the given data
        5
        Query 1
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 2
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 3
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 4
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 5
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 6
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 7
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 8
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 9
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 10
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 11
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 12
        Query does not exist
        Query is close to correct, results are not correct
        Correct results, query is not optimal
        Correct query and correct results. Notes written to explain query.
        5
        Query 13
        Query does...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here