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.
Note: This assignment was created with MySQL in mind. Therefore, dates, numbers, etc. have been set up with that tool in mind.
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.
- Display all contents of the Clients table
- First names, last names, ages and occupations of all clients
- First and last names of clients that borrowed books in March 2018
- First and last names of the top 5 authors clients borrowed in 2017
- Nationalities of the least 5 authors that clients borrowed during the years 2015-2017
- The book that was most borrowed during the years 2015-2017
- Top borrowed genres for client born in years 1970-1980
- Top 5 occupations that borrowed the most in 2016
- Average number of borrowed books by job title
- Create a VIEW and display the titles that were borrowed by at least 20% of clients
- The top month of borrows in 2017
- Average number of borrows by age
- The oldest and the youngest clients of the library
- 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 AuthorNationality1 Sofia Smith Canada2 Maria Brown Brazil3 Elena Martin Mexico4 Zoe Roy France5 Sebastian Lavoie Canada6 Dylan Garcia Spain7 Ian Cruz Mexico8 Lucas Smith USA9 Fabian Wilson USA10 Liam Taylor Canada11 William Thomas Great Britain12 Logan Moore Canada13 Oliver Martin France14 Alysha Thompson Canada15 Isabelle Lee Canada16 Emily Clark USA17 John Young China18 David Wright Canada19 Thomas Scott Canada20 Helena Adams Canada21 Sofia Carter USA22 Liam Parker Canada23 Emily Murphy USA
Book table:
BookID BookTitle BookAuthor Genre1 Build your database system 1 Science2 The red wall 2 Fiction3 The perfect match 3 Fiction4 Digital Logic 4 Science5 How to be a great lawyer 5 Law6 Manage successful negotiations 6 Society7 Pollution today 7 Science8 A gray park 2 Fiction9 How to be rich in one year 8 Humor10 Their bright fate 9 Fiction11 Black lines 10 Fiction12 History of theater 11 Literature13 Electrical transformers 12 Science14 Build your big data system 1 Science15 Right and left 13 Children16 Programming using Python 1 Science17 Computer networks 14 Science18 Performance evaluation 15 Science19 Daily exercise 16 Well being20 The silver uniform 17 Fiction21 Industrial revolution 18 History22 Green nature 19 Well being23 Perfect football 20 Well being24 The chocolate love 21 Humor25 Director and leader 22 Society26 Play football every week 20 well being27 Maya the bee 13 Children28 Perfect rugby 20 Well being29 The end 23 Fiction30 Computer security 1 Science31 Participate 22 Society32 Positive figures 3 Fiction
Client table:
ClientID ClientFirstName ClientLastName ClientDoB Occupation1 Kaiden Hill 2006 Student2 Alina Morton 2010 Student3 Fania Brooks 1983 Food Scientist4 Courtney Jensen 2006 Student5 Brittany Hill 1983 Firefighter6 Max Rogers 2005 Student7 Margaret McCarthy 1981 School Psychologist8 Julie McCarthy 1973 Professor9 Ken McCarthy 1974 Securities Clerk10 Britany O’Quinn 1984 Violinist11 Conner Gardner 1998 Licensed Massage Therapist12 Mya Austin 1960 Parquet Floor Layer13 Thierry Rogers 2004 Student14 Eloise Rogers 1984 Computer Security Manager15 Gerard Jackson 1979 Oil Exploration Engineer16 Randy Day 1986 Aircraft Electrician17 Jodie Page 1990 Manufacturing Director18 Coral Rice 1996 Window Washer19 Ayman Austin 2002 Student20 Jaxson Austin 1999 Repair Worker21 Joel Austin 1973 Police Officer22 Alina Austin 2010 Student23 Elin Austin 1962 Payroll Clerk24 Ophelia Wolf 2004 Student25 Eliot McGuire 1967 Dentist26 Peter McKinney 1968 Professor27 Annabella Henry 1974 Nurse28 Anastasia Baker 2001 Student29 Tyler Baker 1984 Police Officer30 Lilian Ross 1983 Insurance Agent31 Thierry Arnold 1975 Bus Driver32 Angelina Rowe 1979 Firefighter33 Marcia Rowe 1974 Health Educator34 Martin Rowe 1976 Ship Engineer35 Adeline Rowe 2005 Student36 Colette Rowe 1963 Professor37 Diane Clark 1975 Payroll Clerk38 Caroline Clark 1960 Dentist39 Dalton Clayton 1982 Police Officer40 Steve Clayton 1990 Bus Driver41 Melanie Clayton 1987 Computer Engineer42 Alana Wilson 2007 Student43 Carson Byrne 1995 Food Scientist44 Conrad Byrne 2007 Student45 Ryan Porter 2008 Student46 Elin Porter 1978 Computer Programmer47 Tyler Harvey 2007 Student48 Arya Harvey 2008 Student49 Serena Harvey 1978 School Teacher50 Lilly Franklin 1976 Doctor51 Mai Franklin 1994 Dentist52 John Franklin 1999 Firefighter53 Judy Franklin 1995 Firefighter54 Katy Lloyd 1992 School Teacher55 Tamara Allen 1963 Ship Engineer56 Maxim Lyons 1985 Police Officer57 Allan Lyons 1983 Computer Engineer58 Marc Harris 1980 School Teacher59 Elin Young 2009 Student60 Diana Young 2008 Student61 Diane Young 2006 Student62 Alana Bird 2003 Student63 Anna Becker 1979 Security Agent64 Katie Grant 1977 Manager65 Joan Grant 2010 Student66 Bryan Bell 2001 Student67 Belle Miller 1970 Professor68 Peggy Stevens 1990 Bus Driver69 Steve Williamson 1975 HR Clerk70 Tyler Williamson 1999 Doctor71 Izabelle Williamson 1990 Systems Analyst72 Annabel Williamson 1960 Cashier73 Mohamed Waters 1966 Insurance Agent74 Marion Newman 1970 Computer Programmer75 Ada Williams 1986 Computer Programmer76 Sean Scott 1983 Bus Driver77 Farrah Scott 1974 Ship Engineer78 Christine Lambert 1973 School Teacher79 Alysha Lambert 2007 Student80 Maia Grant 1984 School Teacher
Borrower table:
BorrowID ClientID BookID BorrowDate1 35 17 2016-07-202 1 3 2017-04-193 42 8 2016-10-034 62 16 2016-04-055 53 13 2017-01-176 33 15 2015-11-267 40 14 2015-01-218 64 2 2017-09-109 56 30 2017-08-0210 23 2 2018-06-2811 46 19 2015-11-1812 61 20 2015-11-2413 58 7 2017-06-1714 46 16 2017-02-1215 80 21 2018-03-1816 51 23 2015-09-0117 49 18 2015-07-2818 43 18 2015-11-0419 30 2 2018-08-1020 48 24 2015-05-1321 71 5 2016-09-0522 35 3 2016-07-0323 57 1 2015-03-1724 23 25 2017-08-1625 20 12 2018-07-2426 25 7 2015-01-3127 72 29 2016-04-1028 74 20 2017-07-3129 53 14 2016-02-2030 32 10 2017-07-2431 12 15 2018-04-2532 77 13 2017-06-0933 30 4 2017-10-2434 37 24 2016-01-1435 27 26 2017-06-0536 1 16 2018-05-0637 21 9 2016-03-1938 69 28 2017-03-2939 17 19 2017-03-1440 8 9 2016-04-2241 63 18 2015-01-2542 65 20 2016-10-1043 51 19 2015-07-2844 23 12 2017-01-2545 17 4 2017-04-1846 68 5 2016-09-0647 46 13 2017-09-3048 15 13 2017-07-0549 11 19 2017-12-1450 78 15 2017-01-2651 47 9 2015-03-0352 68 7 2016-05-2653 37 26 2017-02-0654 48 27 2015-12-3055 9 21 2017-10-2156 29 8 2018-04-0157 64 18 2017-08-2958 61 26 2018-02-2159 39 28 2016-07-2660 73 18 2018-08-2261 11 13 2018-01-1762 45 6 2016-07-2063 33 13 2018-03-1864 10 17 2016-06-0665 28 18 2017-02-1766 51 3 2016-12-0967 29 2 2015-09-1868 28 30 2017-09-1469 74 20 2015-12-1270 15 22 2015-01-1471 57 8 2017-08-2072 2 5 2015-01-1873 74 12 2018-04-1474 51 10 2016-02-2575 25 17 2015-02-2476 45 21 2017-02-1077 27 25 2016-08-0378 32 28 2016-06-1579 71 21 2017-05-2180 75 26 2016-05-0381 56 32 2015-12-2382 26 32 2015-05-1683 66 32 2015-05-3084 57 18 2017-09-1585 40 15 2016-09-0286 65 4 2017-08-1787 54 7 2015-12-1988 29 4 2017-07-2289 44 9 2017-12-3190 56 31 2015-06-1391 17 4 2015-04-0192 35 16 2018-07-1993 22 18 2017-06-2294 39 24 2015-05-2995 63 14 2018-01-2096 53 21 2016-07-3197 40 9 2016-07-1098 52 4 2017-04-0599 27 20 2016-09-04100 72 29 2015-12-06101 49 16 2017-12-19102 6 12 2016-12-04103 74 31 2016-07-27104 48 32 2016-06-29105 69 2 2016-12-27106 60 32 2017-10-29107 45 22 2017-06-12108 42 15 2017-05-14109 79 8 2016-10-13110 70 18 2016-12-04111 34 8 2016-03-06112 43 8 2015-12-19113 42 32 2016-04-20114 67 5 2017-03-06115 80 25 2015-06-23116 54 11 2017-05-03117 34 28 2017-08-30118 65 20 2017-08-26119 61 19 2018-01-05120 38 12 2018-01-17121 51 4 2016-05-13122 7 16 2016-03-17123 46 16 2016-11-25124 75 30 2018-08-12125 72 32 2015-03-12126 44 17 2015-06-15127 68 15 2016-02-21128 21 1 2016-06-19129 14 25 2016-10-10130 68 21 2016-05-27131 35 20 2015-03-19132 16 27 2016-08-08133 79 31 2018-03-07134 14 17 2018-04-28135 29 28 2018-03-11136 41 4 2018-08-08137 42 3 2016-02-23138 45 3 2017-07-10139 36 16 2018-07-19140 36 30 2015-08-07141 54 32 2018-03-14142 61 15 2017-03-28143 1 13 2018-05-17144 43 1 2015-05-14145 37 14 2015-07-30146 62 17 2015-09-19147 50 22 2016-12-02148 45 1 2016-07-24149 32 17 2018-03-10150 13 28 2016-02-14151 15 9 2018-08-11152 10 19 2018-08-29153 66 3 2016-11-27154 68 29 2017-07-12155 21 14 2018-06-27156 35 9 2016-01-22157 17 24 2016-08-25158 40 21 2015-07-09159 1 24 2016-03-28160 70 27 2015-07-10161 80 26 2016-04-24162 29 5 2015-10-18163 76 12 2018-04-25164 22 4 2016-12-24165 2 2 2017-10-26166 35 13 2016-02-28167 40 8 2017-10-02168 68 9 2016-01-03169 32 5 2016-11-13170 34 17 2016-09-15171 34 16 2018-04-13172 80 30 2016-10-13173 20 32 2015-11-17174 36 10 2017-09-01175 78 12 2018-06-27176 57 8 2016-03-22177 75 11 2017-06-27178 71 10 2015-08-01179 48 22 2015-09-29180 19 16 2016-02-21181 79 30 2018-08-20182 70 13 2016-09-16183 30 6 2017-02-10184 45 12 2017-10-12185 30 27 2016-11-23186 26 3 2016-08-13187 66 6 2017-01-14188 47 15 2016-02-10189 53 30 2018-08-08190 80 16 2016-03-31191 70 13 2018-02-03192 14 25 2016-03-27193 46 22 2016-01-13194 30 32 2015-08-06195 60 14 2016-11-27196 14 13 2018-05-23197 71 15 2016-06-22198 38 21 2015-12-27199 69 30 2017-04-29200 49 31 2018-06-03201 28 28 2015-05-29202 49 3 2016-08-30203 75 1 2015-10-29204 78 3 2017-05-12205 43 18 2015-03-25206 27 21 2016-02-22207 64 22 2015-04-03208 21 11 2017-12-09209 66 29 2016-12-20210 45 13 2017-04-15211 48 30 2015-01-31212 20 25 2017-12-20213 41 20 2018-01-29214 51 12 2015-07-05215 5 1 2015-04-12216 40 3 2018-02-24217 79 4 2018-06-27218 15 10 2016-11-01219 42 22 2016-12-28220 17 9 2018-01-29221 38 13 2016-05-09222 79 2 2017-12-06223 74 3 2015-12-07224 46 8 2016-06-05225 78 22 2018-08-11226 45 2 2015-04-20227 72 31 2015-11-11228 18 17 2015-03-21229 29 3 2017-08-13230 66 11 2018-06-05231 36 16 2016-04-28232 26 2 2016-10-23233 32 1 2017-10-31234 62 14 2017-07-25235 12 4 2015-07-08236 38 32 2015-02-24237 29 16 2016-07-28238 36 25 2017-05-07239 76 7 2015-06-13240 28 16 2016-08-15241 60 13 2016-08-26242 8 3 2017-07-28243 25 1 2016-07-30244 62 29 2018-08-24245 51 8 2016-09-01246 27 23 2015-02-08247 69 12 2018-06-25248 51 12 2015-07-04249 7 4 2015-05-01250 31 15 2017-10-29251 14 23 2015-01-15252 14 1 2018-05-21253 39 25 2015-12-26254 79 24 2016-05-31255 40 15 2016-03-18256 51 13 2018-04-13257 61 1 2015-02-11258 15 24 2018-03-02259 10 22 2018-01-21260 67 10 2017-07-08261 79 11 2016-12-11262 19 32 2016-05-04263 35 11 2017-08-01264 27 13 2017-12-15265 30 22 2015-12-22266 8 7 2015-06-26267 70 9 2016-03-20268 56 18 2016-01-29269 13 19 2015-03-06270 61 2 2016-06-18271 47 13 2017-09-18272 30 22 2016-02-19273 18 22 2016-12-31274 34 29 2017-10-27275 32 21 2015-06-03276 9 28 2016-03-30277 62 24 2015-03-23278 44 22 2017-04-29279 27 5 2015-03-25280 61 28 2017-07-14281 5 13 2016-12-04282 43 19 2018-03-15283 34 19 2016-06-05284 35 5 2018-02-19285 13 12 2016-09-23286 74 18 2016-12-26287 70 31 2017-08-15288 42 17 2016-06-15289 51 24 2018-07-30290 45 30 2015-01-15291 70 17 2017-10-07292 77 7 2017-01-06293 74 25 2015-09-25294 47 14 2018-02-01295 10 2 2017-04-18296 16 21 2016-10-03297 48 5 2016-09-17298 72 3 2017-02-10299 26 23 2016-03-01300 49 23 2016-10-25
Related Lessons & References
This assignment covers material presented in the course. You can refer to the following lessons for guidance:
- Database Table: Design & Conventions
- Advanced SQL Query Syntax
- SQL Views: Definition & Example
- Advanced SQL Subqueries: Use & Examples
- SQL: Create Index
Other lessons you can reference to help with using MySQL include:
- MySQL Commands: Lists and Examples
- Practical Application for Introduction to SQL: Installing Sample Data for MySQL
- Practical Application for Relational Databases & SQL: Creating a Database in MySQL
Note: Students typically use Excel and other tools to create the INSERT statements. They put each line on a row in Excel and then wrap an INSERT around the whole thing, like this:
INSERT INTO tblMyTable VALUES
(1, 25, 17, STR_TO_DATE(’20/07/2016′, ‘%d/%m/%Y’)),
(2, 125, 13, STR_TO_DATE(’20/07/2017′, ‘%d/%m/%Y’));
And so on
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 PointsCreate 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 5Define 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 5Create 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 5Populate the tables with data Tables are not populated by the given data Some tables are populated by part of the given data All tables are populated by part of the given data All tables are correctly populated with the given data 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 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. 5Query 13 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. 5Query 14 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. 5Create the required indexes No indexes chosen Indexes are poorly chosen, queries are not correct Indexes are poorly chosen, queries are correct Indexes are correctly chosen, queries are correctly written 5Create required view No view created The syntax of the view creation is not correct The view is correctly created but not correctly used The view is correctly created and used 5Total