Database Programming - Assignment: Creating & Manipulating a Database

Assignm

Database Programming - Assignment: Creating & Manipulating a Database

Other / Other
22 Dec 2021
Price: $40
Other / Other

Database Programming - Assignment: Creating & Manipulating a Database

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.

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
Least 5 author nationalities 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	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

Lost Password