University Data Management Assignment: Database Design and SQL Queries

This document provides a detailed solution to a data management assignment, addressing key concepts in database systems. The assignment covers the components of DBMS architecture, including connection managers, DML compilers, and various utilities. It explores concurrency control mechanisms, such as lock-based and timestamp-based protocols, and their role in managing concurrent transactions in multi-user environments. The solution includes an ER diagram for a cricket sports league database, demonstrating entity relationships and primary/foreign keys. It also analyzes relational database design, identifying foreign keys and assessing referential integrity. Furthermore, the assignment delves into Total Data Quality Management (TDQM) and data governance frameworks, highlighting their importance in managing and improving data quality. The solution also provides SQL queries for retrieving and manipulating data, including queries to list hotels, guests, and search XML data. Finally, the document discusses different methods for searching XML data, such as full-text search, XQuery, and SQL functions like 'contains' and XPath.

Running Head: DATA MANAGEMENT
DATABASE MANAGEMENT
Name of the Student:
Name of the University:
Author Note:

1DATABASE MANAGEMENT
Table of Contents
Answer to the question 1:...................................................................................................2
Answer to the question 2:...................................................................................................2
Answer to the question 3:...................................................................................................5
Answer to the question 4:...................................................................................................5
Answer to the question 5:...................................................................................................7
Answer to the question 6:...................................................................................................7
Answer to the question 7:...................................................................................................8
Bibliography........................................................................................................................9

2DATABASE MANAGEMENT
Answer to the question 1:
A. The key components of the DBMS architecture may vary with the implementation and
with different vendors. Some of the components may be absent or added according to
the vendor. The most important components of the DBMS are the connection manager,
the security manager, another one is the DDL compiler, and it also contains several
database utilities, one query processor and a storage manager. These are many ways
to interact with the DBMS. The catalog is used to store the DDL statements that create
the data definition. A command line interface is used to execute the interactive queries.
The interaction of the components depends on the task of the database that is being
executed. The connection manager helps in the connection of the database. The setup
can be done locally or over a network, and setting up the database over a network is
more common.
B. The DML compiler is used to compile the data manipulation statements that is being
stated in the DML. DML is the data manipulation language that is used to fetch, or insert
or delete or alter any information in the database. In a procedural DML user specifies
the data that is needed and in what way to access that data. In case of a non-procedural
or also known as declarative DML the user specifies only the data which is needed. The
non-procedural DML is also famous as high level data manipulation language. The
declarative DML is more efficient. Another name for procedural DML is record-at-a-time
DML and the DBMS which uses procedural DML does not have a query processor. The
data is accessed using the memory pointers. The declarative DML is more of a set-at-a-
time DML which can access a set of records at once to provide the application.
C. Some examples of the DBMS utilities are reorganization utility, which reorganizes the
data automatically to provide improved performance. Some other DBMS utilities are
performance monitoring utility, user management utility, and backup and recovery utility.
Some example of DBMS interfaces are web-based interface, command line interface,
stand-alone query language interface, network interface and these provided the
interaction of the DBMS with the database administrators and other parties.

3DATABASE MANAGEMENT
Answer to the question 2:
A. In a multi-user database management system concurrency control is the activity of
managing the parallel execution of transactions. The concurrency control method is
used to manage concurrent operations on a database without creating any conflicts. The
purpose of developing a database is to provide access to the shared data to multiple
users all at the same time. Accessing the data concurrently by multiple users is easy if
all the users are only reading the data. If in a multi user environment all the users are
accessing the data at the same time and at least one user is updating the data then,
there may arise some inconsistency. The concurrency control can be classified into two
types: one is lock-based protocol and another is the timestamp-based protocol.
Example of a transaction in a multi user environment. In a company’s database
all the customer information is stored. If two users access a customer record
simultaneously and some changes are made by both of them in the customer record
then there might arise some problems regarding the data integrity. The table below
shows the data accessing by both the users and the problem related to that.
User A User B Problem The customer data
that is being stored
in the database
Access and open
the customer data
“Rose Hill” “Gold
member”
The user change
the customer
surname. The new
surname will be
“Williams”
Now, user B opens
the customer
record.
Here, the user B
will see the name
“Rose Hill” and not
“Rose Williams”
The user changes At this point, the

4DATABASE MANAGEMENT
the membership to
“platinum”.
user A is unaware
of the change that
is being made by
the user B of the
membership level
and both users are
accessing the data
concurrently but
can see different
sets of information.
User A saves the
customer data.
Here, the user B is
unaware of the
changes made by
user A to the
database.
“Rose William”
“Gold member”
Now the user B
saves the changes
to the database.
The action of the
user B has revised
the surname that
was being changed
and saved by user
A. At this point both
the users are
unaware of the
information that is
lost.
“Rose Hill”
“Platinum member”
The problem that is being described above can be prevented using the concurrency
control method to safely access the shared information and suppressing the risk of
collision. This is accomplished by preventative locking or detection of the collision.

5DATABASE MANAGEMENT
B. If the name of the “CS” department changes to “CSSE” then in the student table the
data in the major column needs to be updated. In the prerequisite table the
course_number and the prerequisite_number has to be updated with the new code. The
department column in the course also needs update.
Answer to the question 3:
A. The ER diagram show a cricket sports league where there are many teams and these
teams contain many players. Each team participates in one or more games and the
every player does not participate in each game. The database will keep track of the
players that participate in each game and in which position they played in that game as
well as will store the result for the game.
In the above ER diagram, the team entity has a primary key team_id which is used in
both the Game and Players entity and the team_id serves as the foreign key in that
case.

6DATABASE MANAGEMENT
Answer to the question 4:
A. In the relation Option the Apartment# is a foreign key as the attribute Apartment#
serves as the primary key for the Apartment relation. In the same way, the Apartment#
serves as the foreign key for the relation Booking. Another foreign key is Agent_id in the
Booking relation because Agent_id serves as the primary key for the Agent relation.
B. Apartment
Apartment# Model Address Model Price_perSquareFt
01 2-BHK 24, street 500
02 2-BHK 25, road 650
03 3-BHK Down lane 660
04 3-BHK 5, street 715
Option
Apartment# Option_name Extra_price
01 Modular kitchen 650
02 Modular kitchen 660
03 Security Camera 950
04 Security Camera 990
Booking
Agent_id Apartment# Date Booking_price
01 02 2/5/1994 14500
02 03 3/9/1998 19000
03 05 16/5/2004 32000
Agent
Agent_id Name Phone
01 Josh Radon 78498726582

7DATABASE MANAGEMENT
02 John Smith 26546516516
In the above provided relations the Booking_price, Extra_price and the
Price_perSquareFt stores data in dollar. The relations are populated with few sample
tuples. In the booking relation the third tuple which has an Agent_id 03 is a violation of
the referential integrity constraints as the Agent_id 03 is not present in the Agent relation
where the Agent_id serves as the primary key. The first and second tuple in the relation
booking does not violate the referential integrity constraints and thus is a valid data.
Answer to the question 5:
Total Data Quality Management is the philosophy of managing the data. This provides
everyone in an organization the responsibility to deliver quality to the customer. Every
task in the organization is viewed as data in TDQM. The aim is to satisfy the customer
requirements at every stage for maximum satisfaction of the ultimate customer and with
the lowest cost. This method involves human resources in order to improve the quality of
the product or the services provided by an organization. Database migration is
supported by TDQM. There are total of four phases in the TDQM which are definition
phase, measurement phase, after that comes the analysis phase and lastly the
improvement phase.
The data governance framework is referred to as the process of building a model
to manage the enterprise data. The guideline and the rules for the engagement of
business and activities of the management are set within the framework. These set of
rules specially apply to the activities that deals with creation and manipulation of data.
The management of an organization should make good decision that can actually
produce results for the organization. The guideline and rules are important for this
reason to make a good decision that can benefit the organization. The data governance
framework allows an organization to make informed decision on managing the data and
reduce complexity, cost and the risks. It also ensure the organization can satisfy the
ever-growing demand.

8DATABASE MANAGEMENT
Answer to the question 6:
A. The SQL query to get the list of hotels with full details is:
SELECT * FROM Hotel;
B. The SQL query to get the list of all the hotel in New York is:
SELECT * FROM Hotel WHERE hotelCity = ‘New York’;
C. The SQL query to list the guests in New York in descending order by the last name
is:
SELECT * FROM Guest WHERE guestAddress LIKE ‘%New York%’ ORDER BY
lastName;
Answer to the question 7:
There are several ways to search the XML data. One of the most used is the full-text
search. This type of search is supported by the documents that are being managed by
the Oracle databases. In this type of search the whole word is searched rather than the
substrings. This also supports some language-based as well as word-based searches.
XQuery is another language that can be used to search XML data and it was developed
by W3C or the World Wide Web Consortium. The XQuery language was developed to
meet certain requirements to query and modify XML data. Contains is a SQL function
that can be used to search XML data. Similarly XPath function is also another example
that can be used to search XML data. The XPath function ora:contains is used within an
XPath expression which is present within an XQuery expression. The XPath function
does not require any index but if an index can be used to improve the performance.

9DATABASE MANAGEMENT
Bibliography
Behzadnia, P., Tu, Y.C., Zeng, B. and Yuan, W., 2017. Energy-Aware Disk Storage
Management: Online Approach with Application in DBMS. arXiv preprint
arXiv:1703.02591.
Brennan, S., 2016. An XPath Query Evaluator for Filesystems EECS 433 Final Project.
Castagna, G., Im, H., Nguyễn, K. and Benzaken, V., 2015, April. A core calculus for
XQuery 3.0. In European Symposium on Programming Languages and Systems (pp.
232-256). Springer, Berlin, Heidelberg.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Ghandeharizadeh, S., Huang, H. and Nguyen, H., 2019, May. Nova: Diffused Database
Processing Using Clouds of Components [Vision Paper]. In International Conference:
Beyond Databases, Architectures and Structures (pp. 3-14). Springer, Cham.
Guo, B., Yu, J., Liao, B., Yang, D. and Lu, L., 2017. A green framework for DBMS based
on energy-aware query optimization and energy-efficient query processing. Journal of
Network and Computer Applications, 84, pp.118-130.
Karau, H., Konwinski, A., Wendell, P. and Zaharia, M., 2015. Learning spark: lightning-
fast big data analysis. " O'Reilly Media, Inc.".
Lemahieu, W., vanden Broucke, S. and Baesens, B., 2018. Principles of Database
Management: The Practical Guide to Storing, Managing and Analyzing Big and Small
Data. Cambridge University Press.
Marrara, S. and Pasi, G., 2016. Fuzzy Approaches to Flexible Querying in XML
Retrieval. International Journal of Computational Intelligence Systems, 9(sup1), pp.95-
103.
White, T., 2011. Hadoop. オオオオオ オオオオ・.