Analysis of Structured Query Language and Server Services in Databases

This report examines the concepts of Structured Query Language (SQL) and server services within the context of database management. It begins by evaluating database normalization, explaining its necessity in maintaining data integrity and efficiency, covering 1NF, 2NF, 3NF, BCNF, and 4NF. A dependency diagram is drawn and the conversion of a student record table into 3NF is demonstrated. The report then explores the purpose of indexing in databases, detailing various index types such as primary, secondary, clustering, dense, sparse, and multilevel indexes, with illustrative examples. Key features of indexed tables and different types of views, including information schema, catalog, and dynamic management views, are also discussed. The report provides a comprehensive overview of database design principles, data organization, and efficient data retrieval methods, offering valuable insights into database management and optimization.

1
Running head: STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Structured Query Language and Server Services
Student’s Name
School Affiliation

2
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Table of Contents
Question 01......................................................................................................................................3
Question 03......................................................................................................................................6
References......................................................................................................................................14

3
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Question 01
a) Evaluate normalization. Explain why normalization is required in the good database
system.
Database normalization – refers to the method of arranging columns as well as tables of a
relational database to minimize redundancy of data and advance the data integrity as well as the
efficiency of information stored. Normalization can also refer to the approach in which poorly
made tables are decomposed through breaking up table attributes into smaller tables which have
desired features (Coronel, and Morris, 2016). Normalization organizes attributes in tables in
respect of dependencies between attributes, guaranteeing that the dependencies are correctly
emphasized by the integrity of database constraints. Normalization might be obtained by
deploying formal policies such as synthesis as well as decomposition. There are several kinds of
normalization which include the following:
1NF – First Normal Form – here no two rows of details should consist of a repeating group of
data, every table should be arranged into rows as well as every row must possess a primary key
which differentiates it as unique.
2NF – Second Normal Form – here it is compulsory to have any partial dependency of every
column on key which is primary implying that for a table which possesses concatenated key that
is primary, every column of the table which is not portion of the primary key should rely upon
the whole concatenated for availability, failing that the table fails Second normal form.
3NF – Third Normal Form – this is database rule that permits you to correctly organize our
tables by creating upon the normalization of database rules offered by 1NF as well as 2NF.
BCNF – Boyce Codd Normal Form – this form is concerned with a kind of anomalies which are
not addressed by 3NF, therefore, a 3NF table that does not possess multiple overlapping
candidate keys is termed to be BCNF.
4NF – Form Normal Form – this is compulsory to meet the whole needs of 3NF. Attributes from
one or many rows of the table might not lead in more than one rows the similar table resulting in
multi-valued dependencies.

4
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
The normalization techniques aids in eliminating data redundancies which cannot be controlled,
hence eliminating the information anomalies as well as the integrity of data challenges which are
generated by such redundancies. Normalization can create controlled redundancy which lets us
correctly connect database tables, however, redundancy in the data is not needed, again
manageable redundancies are included in order to meet the requirements of the users as well as
the efficiency of processing. This is attained by taking the ordered method using normalization in
order every table in the database depicts only a single object, whole attributes are identified by
one key of the table which is primary, and that no information is stored more than one table
except it is relevant (Zheng et al., 20015). Normalization guarantees that the attributed linked
together in such a way in which redundancies are eliminated otherwise in it is controlled. The
presence of data redundancies brings challenges in the database especially while updating and
these problems can result in inconsistencies of data, an update of data anomalies as well as issues
of data integrity.
b) Using the student records shown below, draw a dependency diagram and convert this
table into 3NF (3rd normal form).
STU_NUM DEPT_CODESTU_LNAME
ADVISOR_LNAME ADVISOR_OFFICE ADVISOR_BLDG STU_CLASS

5
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
3NF Entity Name
DEPT_CODE (primary key) department
DEPT_NAME
ADVISOR_LNAME (foreign key)
DEPT_CODE (primary key) Class list
STU_NUM (primary key)
STU_CLASS
STU_NUM student
STU_NAME
STU_MAJOR
STU_GPA
STU_HOURS
ADVISOR_LNAME advisor
ADVISOR_OFFICE
ADVISOR_BLDG
ADVISOR_PHONE
COLLEGE_NAME
Question 03
a. Explain the purpose of indexing in the database?
STU_NUM DEPT_CODE ADVISOR_LNAME STU_HOURS

6
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Database indexing refers to a data structure approach to efficiently access information from the
database files in reference to their attributes in which the indexing has been created. It is utilized
to advance the rate of retrieval activities on database table based at more writes as well as storage
memory to upheld the index data structure (Thomas, Silverman, and Nelson, 2015). Indexes are
deployed to rapidly locate records without having to search each row table each time database
records are accessed. Indexes can be generated using one or many columns of a database table,
offering the basis for random lookups as well as efficient access of records which are ordered. In
other words, indexing it is a method to optimize the operation of a database by reducing the
number of storage disks accesses needed when the query is processed and therefore can be
utilized to rapidly locate as well as access the information in a database table. Indexing uses the
index that can be looked up appropriately that also incorporates a less-level memory block
address or link which is direct to the entire row of record it was gotten from (Cragun et al.,
2014). Several databases extend the capacity of indexing by allowing developers to produce
indexes on expression as well as functions. For instance, an index might be formed on upper last
name, that might only hold the uppercase types of the last name zone in the index. The database
index permits a query to accurately access records from the database in which those indexes are
linked to a particular table and comprises of one or many keys.
b. Discuss different types of indexes associated with the table or view along with examples.
The index can be classified in the following types:
Primary index – this is set on the ordered record file. The record file arranged on key space in
which the key field is normally the key which is primary in the relation.
Let's consider the example below:

7
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Secondary index – this is produced from space that is candidate key and contains a unique value
in each record, or non-key with copied values. The following example depicts 3 record block are
required to access all the tuples with lookup key K=20.

8
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Clustering index – it is set up on an organized record file, the data file is arranged on non-key
space. Let's consider the example of a clustering index:

9
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Dense index – this involves index record for each search key value in the database and makes
accessing data faster although needs more space to hold index data itself. Index data consists of
search key values as well as a pointer to the real record on the storage. For example:
Sparse index – here index data are not produced for each search key and an index file consists of
search key as well as a real pointer to the record on the storage. To look up for a record, one
moves by index data and reach the reallocation of the record. When the record you are searching
for is not where you accessed directly by following the index, then the system begins a
sequential search till the needed record is found. For instance:

10
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Multilevel index – index data contains search key values as well as data pointers. The multilevel
index is held on the storage along with the real database documents. As the size of the storage
grows, the size of indexes grows too. There is a great need to uphold the index data in the main
storage in order to speed up the lookups activities. Let us consider the diagram below:
c. Evaluate the key features of indexed tables and types of views with examples.
Indexes have the following features:
Usability – indexes by default are usable., an unusable index is not held by DML activities as
well as neglected by the optimizer. An unusable index might advance the operation of bulk loads
rather than dropping an index then recreating it, u may make the index unusable and the remake
it.
Visibility – indexes are said to be visible by default and can also be invisible. An invisible index
is held by DML activities and is not utilized by default by the optimizer. Invisible indexes are
important for evaluating the removal of index prior to dropping it.
View refers to a logical table and physical object that hold record logically. The views can be
categorized as follows:

11
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Information schema view- let's consider the example below in order to under this view.

12
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
Catalog view – they were launched with SQL server 2005 and utilized to depict database self-
explanation data. Let’s consider the illustration below.
Dynamic management view – they were launched in SQL server 2005, and these views provide
the controller record of the database on the current position of the SQL server machine. Let’s
consider an example of database-scoped dynamic management view below:

13
STRUCTURED QUERY LANGUAGE AND SERVER SERVICES
References
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Zheng, L., Wang, S., Tian, L., He, F., Liu, Z., & Tian, Q. (2015). Query-adaptive late fusion for
image search and person re-identification. In Proceedings of the IEEE conference on
computer vision and pattern recognition (pp. 1741-1750).
Thomas, J. R., Silverman, S., & Nelson, J. (2015). Research methods in physical activity, 7E.
Human Kinetics.
Cragun, B. J., Rice, J. E., Schwarz, P. M., Swope, W. C., & Tran, H. T. (2014). U.S. Patent No.
8,793,231. Washington, DC: U.S. Patent and Trademark Office.