computer science 204

Abhi3735

Untitleddocument-8.pdf

Home >Computer Science homework help >computer science 204

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.

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.

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. Nationalities of the least 5 authors that 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 2016-07-20

2 1 3 2017-04-19

3 42 8 2016-10-03

4 62 16 2016-04-05

5 53 13 2017-01-17

6 33 15 2015-11-26

7 40 14 2015-01-21

8 64 2 2017-09-10

9 56 30 2017-08-02

10 23 2 2018-06-28

11 46 19 2015-11-18

12 61 20 2015-11-24

13 58 7 2017-06-17

14 46 16 2017-02-12

15 80 21 2018-03-18

16 51 23 2015-09-01

17 49 18 2015-07-28

18 43 18 2015-11-04

19 30 2 2018-08-10

20 48 24 2015-05-13

21 71 5 2016-09-05

22 35 3 2016-07-03

23 57 1 2015-03-17

24 23 25 2017-08-16

25 20 12 2018-07-24

26 25 7 2015-01-31

27 72 29 2016-04-10

28 74 20 2017-07-31

29 53 14 2016-02-20

30 32 10 2017-07-24

31 12 15 2018-04-25

32 77 13 2017-06-09

33 30 4 2017-10-24

34 37 24 2016-01-14

35 27 26 2017-06-05

36 1 16 2018-05-06

37 21 9 2016-03-19

38 69 28 2017-03-29

39 17 19 2017-03-14

40 8 9 2016-04-22

41 63 18 2015-01-25

42 65 20 2016-10-10

43 51 19 2015-07-28

44 23 12 2017-01-25

45 17 4 2017-04-18

46 68 5 2016-09-06

47 46 13 2017-09-30

48 15 13 2017-07-05

49 11 19 2017-12-14

50 78 15 2017-01-26

51 47 9 2015-03-03

52 68 7 2016-05-26

53 37 26 2017-02-06

54 48 27 2015-12-30

55 9 21 2017-10-21

56 29 8 2018-04-01

57 64 18 2017-08-29

58 61 26 2018-02-21

59 39 28 2016-07-26

60 73 18 2018-08-22

61 11 13 2018-01-17

62 45 6 2016-07-20

63 33 13 2018-03-18

64 10 17 2016-06-06

65 28 18 2017-02-17

66 51 3 2016-12-09

67 29 2 2015-09-18

68 28 30 2017-09-14

69 74 20 2015-12-12

70 15 22 2015-01-14

71 57 8 2017-08-20

72 2 5 2015-01-18

73 74 12 2018-04-14

74 51 10 2016-02-25

75 25 17 2015-02-24

76 45 21 2017-02-10

77 27 25 2016-08-03

78 32 28 2016-06-15

79 71 21 2017-05-21

80 75 26 2016-05-03

81 56 32 2015-12-23

82 26 32 2015-05-16

83 66 32 2015-05-30

84 57 18 2017-09-15

85 40 15 2016-09-02

86 65 4 2017-08-17

87 54 7 2015-12-19

88 29 4 2017-07-22

89 44 9 2017-12-31

90 56 31 2015-06-13

91 17 4 2015-04-01

92 35 16 2018-07-19

93 22 18 2017-06-22

94 39 24 2015-05-29

95 63 14 2018-01-20

96 53 21 2016-07-31

97 40 9 2016-07-10

98 52 4 2017-04-05

99 27 20 2016-09-04

100 72 29 2015-12-06

101 49 16 2017-12-19

102 6 12 2016-12-04

103 74 31 2016-07-27

104 48 32 2016-06-29

105 69 2 2016-12-27

106 60 32 2017-10-29

107 45 22 2017-06-12

108 42 15 2017-05-14

109 79 8 2016-10-13

110 70 18 2016-12-04

111 34 8 2016-03-06

112 43 8 2015-12-19

113 42 32 2016-04-20

114 67 5 2017-03-06

115 80 25 2015-06-23

116 54 11 2017-05-03

117 34 28 2017-08-30

118 65 20 2017-08-26

119 61 19 2018-01-05

120 38 12 2018-01-17

121 51 4 2016-05-13

122 7 16 2016-03-17

123 46 16 2016-11-25

124 75 30 2018-08-12

125 72 32 2015-03-12

126 44 17 2015-06-15

127 68 15 2016-02-21

128 21 1 2016-06-19

129 14 25 2016-10-10

130 68 21 2016-05-27

131 35 20 2015-03-19

132 16 27 2016-08-08

133 79 31 2018-03-07

134 14 17 2018-04-28

135 29 28 2018-03-11

136 41 4 2018-08-08

137 42 3 2016-02-23

138 45 3 2017-07-10

139 36 16 2018-07-19

140 36 30 2015-08-07

141 54 32 2018-03-14

142 61 15 2017-03-28

143 1 13 2018-05-17

144 43 1 2015-05-14

145 37 14 2015-07-30

146 62 17 2015-09-19

147 50 22 2016-12-02

148 45 1 2016-07-24

149 32 17 2018-03-10

150 13 28 2016-02-14

151 15 9 2018-08-11

152 10 19 2018-08-29

153 66 3 2016-11-27

154 68 29 2017-07-12

155 21 14 2018-06-27

156 35 9 2016-01-22

157 17 24 2016-08-25

158 40 21 2015-07-09

159 1 24 2016-03-28

160 70 27 2015-07-10

161 80 26 2016-04-24

162 29 5 2015-10-18

163 76 12 2018-04-25

164 22 4 2016-12-24

165 2 2 2017-10-26

166 35 13 2016-02-28

167 40 8 2017-10-02

168 68 9 2016-01-03

169 32 5 2016-11-13

170 34 17 2016-09-15

171 34 16 2018-04-13

172 80 30 2016-10-13

173 20 32 2015-11-17

174 36 10 2017-09-01

175 78 12 2018-06-27

176 57 8 2016-03-22

177 75 11 2017-06-27

178 71 10 2015-08-01

179 48 22 2015-09-29

180 19 16 2016-02-21

181 79 30 2018-08-20

182 70 13 2016-09-16

183 30 6 2017-02-10

184 45 12 2017-10-12

185 30 27 2016-11-23

186 26 3 2016-08-13

187 66 6 2017-01-14

188 47 15 2016-02-10

189 53 30 2018-08-08

190 80 16 2016-03-31

191 70 13 2018-02-03

192 14 25 2016-03-27

193 46 22 2016-01-13

194 30 32 2015-08-06

195 60 14 2016-11-27

196 14 13 2018-05-23

197 71 15 2016-06-22

198 38 21 2015-12-27

199 69 30 2017-04-29

200 49 31 2018-06-03

201 28 28 2015-05-29

202 49 3 2016-08-30

203 75 1 2015-10-29

204 78 3 2017-05-12

205 43 18 2015-03-25

206 27 21 2016-02-22

207 64 22 2015-04-03

208 21 11 2017-12-09

209 66 29 2016-12-20

210 45 13 2017-04-15

211 48 30 2015-01-31

212 20 25 2017-12-20

213 41 20 2018-01-29

214 51 12 2015-07-05

215 5 1 2015-04-12

216 40 3 2018-02-24

217 79 4 2018-06-27

218 15 10 2016-11-01

219 42 22 2016-12-28

220 17 9 2018-01-29

221 38 13 2016-05-09

222 79 2 2017-12-06

223 74 3 2015-12-07

224 46 8 2016-06-05

225 78 22 2018-08-11

226 45 2 2015-04-20

227 72 31 2015-11-11

228 18 17 2015-03-21

229 29 3 2017-08-13

230 66 11 2018-06-05

231 36 16 2016-04-28

232 26 2 2016-10-23

233 32 1 2017-10-31

234 62 14 2017-07-25

235 12 4 2015-07-08

236 38 32 2015-02-24

237 29 16 2016-07-28

238 36 25 2017-05-07

239 76 7 2015-06-13

240 28 16 2016-08-15

241 60 13 2016-08-26

242 8 3 2017-07-28

243 25 1 2016-07-30

244 62 29 2018-08-24

245 51 8 2016-09-01

246 27 23 2015-02-08

247 69 12 2018-06-25

248 51 12 2015-07-04

249 7 4 2015-05-01

250 31 15 2017-10-29

251 14 23 2015-01-15

252 14 1 2018-05-21

253 39 25 2015-12-26

254 79 24 2016-05-31

255 40 15 2016-03-18

256 51 13 2018-04-13

257 61 1 2015-02-11

258 15 24 2018-03-02

259 10 22 2018-01-21

260 67 10 2017-07-08

261 79 11 2016-12-11

262 19 32 2016-05-04

263 35 11 2017-08-01

264 27 13 2017-12-15

265 30 22 2015-12-22

266 8 7 2015-06-26

267 70 9 2016-03-20

268 56 18 2016-01-29

269 13 19 2015-03-06

270 61 2 2016-06-18

271 47 13 2017-09-18

272 30 22 2016-02-19

273 18 22 2016-12-31

274 34 29 2017-10-27

275 32 21 2015-06-03

276 9 28 2016-03-30

277 62 24 2015-03-23

278 44 22 2017-04-29

279 27 5 2015-03-25

280 61 28 2017-07-14

281 5 13 2016-12-04

282 43 19 2018-03-15

283 34 19 2016-06-05

284 35 5 2018-02-19

285 13 12 2016-09-23

286 74 18 2016-12-26

287 70 31 2017-08-15

288 42 17 2016-06-15

289 51 24 2018-07-30

290 45 30 2015-01-15

291 70 17 2017-10-07

292 77 7 2017-01-06

293 74 25 2015-09-25

294 47 14 2018-02-01

295 10 2 2017-04-18

296 16 21 2016-10-03

297 48 5 2016-09-17

298 72 3 2017-02-10

299 26 23 2016-03-01

300 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

https://study.com/academy/lesson/database-table-design-conventions.html

https://study.com/academy/lesson/advanced-sql-query-syntax.html

https://study.com/academy/lesson/sql-views-definition-example.html

https://study.com/academy/lesson/advanced-sql-subqueries-use-examples.html

https://study.com/academy/lesson/sql-create-index.html

https://study.com/academy/lesson/mysql-commands-list-examples.html

https://study.com/academy/lesson/practical-application-for-introduction-to-sql-installing-sample-data-for-mysql.html

https://study.com/academy/lesson/practical-application-for-relational-databases-sql-creating-a-database-in-mysql.html

(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 Unacceptabl e (0-1)

Needs Improvement (2-3)

Good (4) Excellent (5) Total Possibl e 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

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

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

Populate 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

Query 1 Query does not exist