Query Writing Skills And Issues Related To Integrity Of Database In Essay.

1. The schema for the LibraryDB database is given below. LibraryDB is a database that keeps track of information about the books and their circulation in a library. 

The primary keys are underlined. The foreign keys are denoted by asterisks (*). 

Borrow (transactionID:text, personID*:text, borrowdate:date, returndate:date)

Book (ISBN:number, title:text, Volnumber:number, year_published:numeric, NoofCopies:number; Price:number; subjectID*:text; PublisherID:text)

Person (personID:text, firstname:text,  lastname:text, address:text, city:text,

       postcode:txt, phonenumber:number)

publisher (publisherID:text, publisherfullname:txt)

subject (subjectID:text, subjectName:text) 

Description of the schema 

borrow -- keeps track of the check-ins and check-outs of the books. 

person -- keeps track of the people who borrow books from the library.

book -- contains information about the books that are available in the library

publisher -- keeps track of the publisher information

subject -- this relation keeps information about the subjects on which the library collection have books (such as Mathematics, Database, etc) 

1. First you need to create the above database structure on MS Access and populate all tables with suitable data (at least 3 records per table) using the following SQL statement.

INSERT into TableName

VALUES (“..”,”..”,.....)  (10 Marks)

1. Write SQL queries (do not use QBE) for the following questions and execute the queries after creating the above database on MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions (2(b) 1- 2(b)6).

(3 marks for each screen shot & remaining marks for the SQL query) 

1. Display details of all the books borrowed. Your result set should be sorted on descending order of the

 (5 Marks) 

2. Display details of all the “Database” books (SubjectName is database) in the library which are published in year 2016.

(Hint: you need to join Book table and Subject table)

(10 Marks) 

3. Display ISBN, title of all the books published after 2012.

 (5 Marks) 

4. Find the title of the most expensive book in the library.

 (10 Marks) 

5. Assume that you want to count how many books in the library which are older than 10 years (i.e year_published is before 2007). Write a query to find the number.

 (10 Marks) 

An agency called WorkForce supplies part-time/temporary staff to hotels throughout Australia. 

1. Determine the Functional Dependencies that exist in the following table. 

WorkForce (Emp_ID,Contract_No, HoursAssignedPerWeek, Hotel_contact_No      HotelNo, HotelLocation)

(5 Marks) 

1. The table shown below lists the time spent by agency staff working at two hotels. The Emp_ID is unique for each employee. 

 Provide examples of insertion, deletion, and modification anomalies that may occur in the above table. Propose a suitable solution to overcome these issues.(10 Marks)  

• NoSQL is a new approach to databases which is different from traditional relational database management systems (RDBMS). Research more about this topic and find out benefits of using NoSQL databases.

 (5 Marks)
  Marking criteria:Marks are allocated for each part as below.   Marking Rubric for Assignment 1 –Part B:  Total Marks 70       

Emp_ID	Contract_No	HoursAssigned PerWeek	Hotel_contact_No	HotelNo	HotelLocation
1177567	MIT1009	17	045566790	H12	Ringwood
1256788	MIT10010	20	056678954	H40	Melbourne CBD
2246769	MIT10011	30	045566790	H12	Ringwood
2254678	MIT10012	30	056678954	H40	Melbourne CBD
3758956	MIT10013	25	045566790	H12	Ringwood
4237890	MIT10014	36	056678954	H40	Melbourne CBD
Section to be included in the report	Description of the section	Marks
1.      Question 1	Query writing Skills- Develop data models and implement DB systems.	50
2.      Question 2	Issues related to integrity of database	20
	Total marks for the Report	70
3.      Assignment 1 Quiz	Assignment 1 Quiz on Moodle	30
	TOTAL	100
Grade/ Mark	Excellent   100%	Very Good   80%	Good   60%	Satisfactory   40%	Unsatisfactory   0%-20%
Query writing Skills Q1   40 marks	Evidence of accurate and well- written queries 100%	Evidence of good query writing skills. 80%	Generally relevant. 60%	Displayed reasonable query writing skills. 40%	Demonstrated little evidence of understanding the topic. 0-20%
Q2 Issues related to integrity of database 15 marks	Demonstrated   excellent ability to think critically. 100%	Demonstrated  an ability to think critically. 80%	Demonstrated reasonable   ability to think. 60%	Demonstrated   some ability to think critically but not complete. 40%	Did not demonstrate ability to think critically. 0-20%.
Q3  critical assessment of database management systems concepts 5  marks	Demonstrated   excellent ability to think critically. 100%	Demonstrated  an ability to think critically. 80%	Demonstrated reasonable   ability to think. 60%	Demonstrated   some ability to think critically but not complete. 40%	Did not demonstrate ability to think critically. 0-20%

The Schema and Tables in the Database

2.A. Functional Dependencies

Functional dependency 1:

EMP_ID à CONTRACT_NO, HOURSASSIGNEDPERWEEK

Functional dependency 2:

CONTRACT_NOà EMP_ID, HOURSASSIGNEDPERWEEK

Functional dependency 3:

HOTELNO àHOTEL_CONTACT_NO, HOTELLOCATION

Functional dependency 4:

HOTEL_CONTACT_NOà HOTELNO, HOTEL_CONTACT_NO

2.B. Insertion, deletion, and modification anomalies

In the given table, insertion, deletion and modification anomalies are present due to the un-normalized form of the relation. Now if the relation is decomposed up to 3^rd normal form then it will eliminate all anomalies. Therefore, the given relation in 1^st normal form is:

Emp_ID

Contract_No

HoursAssigned PerWeek

Hotel_contact_No

HotelNo

HotelLocation

2^nd normal form:

Relation 1:

EMP_ID à CONTRACT_NO, HOURSASSIGNEDPERWEEK

Relation 2:

HOTELNO àHOTEL_CONTACT_NO, HOTELLOCATION

3^rd normal form:

Relation 1:

EMP_ID à CONTRACT_NO, HOURSASSIGNEDPERWEEK, HOTELNO

Relation 2:

HOTELNO àHOTEL_CONTACT_NO, HOTELLOCATION

In the above two decomposed relations in 3^rd NF are suitable solution to overcome these issues.

3. NoSQL

NoSQL can be defined as a method to design database which can easily accommodate a large variety of data models which is inclusive of key values, documents, formats of graph (Moniruzzaman and Hossain, 2013). No SQL stands for Not only SQL which is considered to be an alternative method of approach to database which is traditional in nature. In this method data is placed in various tables and schema of data which is designed in a careful way before the database is built. NoSQL database is considered to be useful for working before the database is built. NoSQL database is considered to be very useful for working various sets of data which is distributed.

The term of NoSQL can be easily applied to some of the database which can be overcome relational database management system (Pokorny, 2013). It is mainly referred to database which is built for the purpose of clustering of large scale database in various kinds of cloud and web services. In this kind of application needs of performance and scalability over checks the various kinds of issue of rigid data consistency.

Various organization round the globe like Google and Amazon makes use of NoSQL database so that they can easily focus various kinds of operational goals and implement relational database where high value of data consistency is required. NoSQL is a kind of database which is used for various web and cloud application which mainly tends to focus on specific idea of management of data (McCreary and Kelly, 2014). It has the ability to process large volume of data and quickly distributing that across various kinds of clusters which are considered to be desirable trait in cloud and web designing. Various developers who intended to create cloud and web systems are more flexible to create flexible schema of data which ultimately focus on the fact it enables fast or rapid changes to application which are generally updated on a continuous basis.                                                                     Figure 1: RDBMS vs NoSQL

Workforce Agency

                                                                     (Source: Created by author)

Key value store is a simple model of data which generally pairs a unique value of key with the value which is associated with it (Moniruzzaman and Hossain, 2013). Documents database is also known as document store is nothing but semi-structured data which is presented in document format. Document database provide developers with option to create and upgrade various kinds of program without the need of any kind of reference schema. Wide column stores organizes data in the form of columns instead of rows. Wide-Column stores can be found in the both forms namely SQL and NoSQL database. The basic structure of NoSQL is generally classified into various kinds of guides. In the various ranges of time vendors have mixed and matched elements in various form of NoSQL.

Graph store can easily organize data as nodes which can easily record in the form of relational database which mainly shows or represent connection between the nodes (McCreary and Kelly, 2014). There is other NoSQL database which have gained importance based on cloud based NoSQL database like Amazon DynamoDB, Google Big table and MangoDB.

There are list of advantages using NoSQL:

• It can easily handle large volume of semi-structured, unstructured data as well as structured data.
• It provides quick iteration and pushing of frequent codes.
• It can easily handle OOPs or Object Oriented Programming language which is considered to be easy and flexible to use.
• It has an efficient architecture which is cheap. 

References  

McCreary, D. and Kelly, A., 2014. Making sense of NoSQL. Shelter Island: Manning, pp.19-20.

Moniruzzaman, A.B.M. and Hossain, S.A., 2013. Nosql database: New era of databases for big data analytics-classification, characteristics and comparison. arXiv preprint arXiv:1307.0191.

Pokorny, J., 2013. NoSQL databases: a step to database scalability in web environment. International Journal of Web Information Systems, 9(1), pp.69-82. 

Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U. and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream Management (pp. 317-336). Springer Berlin Heidelberg.

Gouhar, A., 2017. Database Management System. International Journal of Engineering Science, 11766.

Howard, N., Corcoran, N., Peters, J., Moon, D., Murphy, D., Kerger, M., Crowe, H., Xiberras, P., Hovens, C. and Costello, A., 2016. Caisis: A Prostate Cancer Database Management System for Translational Research.

Reddy, T.B., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., Mallajosyula, J., Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine Database (GOLD) v. 5: a metadata management system based on a four level (meta) genome project classification. Nucleic acids research, 43(D1), pp.D1099-D1106.

Singer, M., 2016, June. The application of a database management system in an energy management system. In Proceedings of the Ninth Power Systems Computation Conference (p. 359). Elsevier.