Systems Analysis and Database Design: Quiz 3 Answer Key

This document provides solutions to a Systems Analysis and Database Design Quiz 3, covering various aspects of database concepts. The solutions address questions related to compound keys, explaining their use in identifying data occurrences with multiple attributes and their role in data normalization. The document also explains Data Definition Language (DDL) and its importance in database interaction, manipulation, and referential integrity. Furthermore, the solutions clarify the use of the distinct keyword in SQL to eliminate duplicate records, providing an example of its application. The document explores character and string data types such as char, varchar, text, and varchar (max), explaining their storage capabilities. The solutions also explain the functionality of the 'like' operator for pattern matching and the 'is' keyword for handling null values in SQL queries. The answers are comprehensive and include relevant references.

Systems Analysis and Database Design: Quiz 3
Name
Institution
Professor
Course
Date

Question Two
A compound key in the database is used to uniquely identify data occurrence with more
than one attribute or columns. Ideally, composite keys are used when there are non-composite
candidate keys. The use of composite keys is dependent on the personal need of any database
developer. It can be used when the developer wants the primary key in a given table has to
achieve a predefined purpose (Asha, Kumar & Kumar 2014, pp. 2232). With the use of
compound keys, it has been possible to normalize data which cannot be achieved with just a
single primary key entity attribute. Finally, DDL is used to strengthen referential integrity to the
data. In cases where data tables have many records, use of single entity in the database does not
guarantee strong entity keys.
Question Six
Data Definition Language (DDL) is a computer and database based language which is
used by database administrators to interact and modify database objects. In this case, database
objects can be table views, indexes, and schemas. In Structured Query Language (SQL), some of
the commonly used DDL’s are create, drop and alter commands (Meijer & Bierman 2011, pp.
30). Through the use of DDL, it has been possible to interact and manipulate database through
the use of Data Manipulation Language (DML). Important to note is that without DDL, it is not
possible to have DML. Therefore, DDL server as the basis of all database operations and
manipulations such as DML and TCL.
Question Seven
The distinct keyword is used in conjunction with other data manipulation language such
as select. The distinct keyword is used in the structured query language (SQL) to eliminate

tables’ duplicates records (Akomolafe, Timothy & Ofere 2014, pp. 318). The main goal of using
distinct keyword help in fetching that exact number of records instead of retrieving unnecessary
data. An example of the use of distinct keyword select records from table gam by correlating the
attribute acid from another table named dtd. In this case, the acid attribute has been used as the
primary key on table gam and as the primary key in a table named dtd.
select distinct foracid, acid, clr_bal_amt from tbaadm.gam where acid in (select acid from
tbaadm.dtd where acid='00108484');
Question Eight
Character and string data types can broadly be classified into; char, varchar (max), text,
and varchar types. With these different data string types, they are used to store various types of
data into database tables. The data accepted in these columns are unique in such a way that a mix
of data attributes cannot be accepted in the database (Asha, Kumar & Kumar 2014, pp. 2232).
The char string type offers a fixed storage size in the specified column. The length of the string
can extend up to 8,000 characters, and when a less short string is assigned to the data type,
trailing spaces are appended to fill the required length. Next, varchar type can store a variable
size of up to 8,000 characters but does not append the trailing spaces. Additionally, text data
types are used to store a large amount of data. Finally, varchar (max) is almost similar to
standard varchar types, but it has different internal structure and functionality as it is much closer
to text data types.

Question Nine
The “like” operator would be able to match all attributes with some characters at the left
side and followed by character “a” and are in the range of m-p and q-t with subjected attributes
starting with specified character values (Kohler, Link & Zhou 2015, pp. 1119). An example can
be from string “data” would be selected if it is between m-p and q-t provided it has character “a”
as the predecessor character.
Question Ten
The SQL keyword “is” mainly used in circumstances where a certain attribute is believed
to have a “null” value. In database language, null attributes mean the data instance is unknown or
not applicable. In cases where data attribute is believed to be null, other operators such as “=, >
and <" are not applicable during the data retrieval process. The “is” keyword is different from
assignment operator “=” in that it is used to search for none-applicable data attributes (Varga,
Janosi & Kalman 2016, pp. 232). On the same note, "=" is used to assign values to variables in
SQL but not in Oracle which use ":=" as the assignment operator.

Bibliography
Akomolafe, D.T., Timothy, N. and Ofere, F., 2014. Using a database management system to
develop and implement an automated motor vehicle management system. European Scientific
Journal, ESJ, 10(24), pp. 313-322.
Asha, N., Kumar, M.V. and Kumar, C.B., 2014. Construction of Database Design-A Reverse
Engineering Process. International Journal of Computer Science and Information Technologies,
Vol. 5 (2), 2231-2234
Kohler, H., Link, S. and Zhou, X., 2015. Possible and certain SQL keys. Proceedings of the
VLDB Endowment, 8(11), pp.1118-1129.
Letkowski, J., 2014. Doing database design with MySQL. Journal of Technology Research, 6(1),
pp.1-15.
Meijer, E. and Bierman, G., 2011. A co-relational model of data for large shared data
banks. Queue, 9(3), p.30.
Varga, V., Janosi, K.T. and Kalman, B., 2016. Conceptual design of document NoSQL database
with formal concept analysis. Acta Polytech. Hungarica, 13(2), pp.229-248.