DABASE: Long Exam Part 3 of 5

Topic: Constraints, DDL and Relational Database

Test I. Multiple Choice: (20 points)
Instructions: Read each statement carefully and choose the letter of the best answer.
1. When can you say that a database has data integrity?
a. The data is consistent and correct. c. Individual items do not contradict one
another.
b. The data has the same values. d. Individual items satisfy all relevant
rules.
2. As a database designer, you should follow relevant rules the way you design the database. Which of
the rules below are you going to consider?
a. company rules b. tax rules c. laws of nature d. All of the above
3. Data on the database has consistency when it:
a. satisfies all relevant rules. b. does not contradict one another.
b. has the same values. d. has no duplicate values.
4. How can you enforce Data Integrity on the database? By using:
a. relational keys. c. database keys.
b. primary keys. d. foreign keys.
5. Data on the database is correct when it:
a. satisfies all relevant rules. b. does not contradict one another.
b. has the same values. d. has no duplicate values.
6. It is a column or group of columns of a table of which the values are always unique.
a. foreign key b. alternate key c. primary key d. none of the
above
7. What is the distinction between a Primary Key and an Alternate Key?
a. A table may have many primary keys but only one Alternate Key.
b. Primary Key is always unique while Alternate Key has duplicates.
c. A table may have many Alternate Keys but only one Primary Key.
d. Primary Key has atomic value while Alternate Key is multi-valued.
8. What is the purpose of using a Foreign Key on the Relation? To ensure:
a. uniqueness and consistency of the data.
b. that data would not contradict each other.
c. referential integrity of the data.
d. no duplication of data values.
9. Andrew Weing inserted a value in the table book. The table will accept null value but each item in the
column is distinct. He tried inserting the same value but the system will generate an error code. What
constraint did not allow Andrew Weing during insertion?
a. unique b. foreign key c. primary key d. composite key
10. You were trying to insert a record to product. As the query is executed, the application software
prompted an error and found out that each attribute or field values were not separated by a character.
Which of the following characters below was missing?
a. semicolon (;) c. colon (:)
b. comma(,) d. parentheses ( () )
11. Yuri Mendez, a database administrator of a software development firm in Cebu, was trying to insert a
record of a transaction made by a customer. However, the table for products was truncated which
created an error upon insertion. What certain constraint did not allow her to insert a record for a
transaction?
a. primary key b. unique c. foreign key d. not null
12. A field or combination of fields that uniquely identify a record is called:
a. field b. record c. data type d. key
13. It refers to the defined rules in DBMS which may be defined during creation of table. This rule may not
allow certain items or records be stored on the database based on the rule specified.
a. constraint b. composite key c. not null d. protocols
14. What certain data type in SQL that will only accept two values?
a. not null b. bit c. boolean d. composite key
15. Argie was using Oracle in his three – tier application. If Oracle is a set of software that is used to define,
store, manipulate and control the data in a database, what is then Oracle?
a. Relational Database c. DBMS
b. database d. Flat-file Database
16. Jimlei had defined a table for products. He noticed that there were two records having the same
ProductID and three records were having reiterated ProductID. What constraint should have been
implemented such that these problems would have not occurred?
a. not null b. foreign key c. unique d. primary key
17. Which of the following SQL clauses is used to combine rows from two or more tables, based on a
common field between them?
a. Join b. Key c. null d. Constraints
18. Which of the following choices below is NOT an example of SQL Join?
a. Inner b. Left c. Right d. Half
19. Joiece was creating a table for all customers. Upon declaring a column for customer name, she thought
that it would accept alphanumeric characters. If you were Joiece, what appropriate data type would
allow you to do so?
a. varchar b. decimal c. string d. char
20. You wanted to clear all records from your table without using DML delete statement. What other
statement would allow you to do so?
a. clear b. delete c. truncate d. remove
Topic: DBMS Constraints, Concepts and Terminologies
Test II: Modified TRUE or FALSE (2 pts each)
Instructions: Write the word TRUE if the statement is true, otherwise CHANGE the underline word/s to
make it correct. Write your answer on your examination booklet.

1. A primary key constraint is defined using composite key keyword which may be defined during defining
the table or using alter. Primary key
2. Top returns scalar value indicating the highest value possible within the specified column of the query.
Max
3. If we mistakenly inserted a value into our table and we would like to modify this value. We use alter
statement to modify a value of the column in our table. Update
4. Lee Kwong would like to know the total of all money collected and stored in his table. Thus, he needs to use
Total function. Sum
5. Maeleine forgot to indicate the column HasReplaced under Damage table specifying that a damaged
product had been replaced. She needed to execute the query: ALTER TABLE tblDamage ADD HasReplaced
bit. TRUE
6. The column brand name under products table would only accept distinct value but may be executed even
though the column and its value may is not specified during insertion. Hence, the column is using Primary
key constraint.
7. You are opted to get the average grade of the student in each subject. AVG aggregate function would
return the average scalar value of a grade. TRUE
8. In orders table, there are items that are stored in the column ProductID that were not present in the
ProductID of Product table. It may accept reiterated ProductID but not null values. Therefore, ProductID is
using Foreign key constraint. Not null
9. In SQL, char data type would allow Jerisse to input alphanumeric and special characters to the column
password.
10. If we would like to know the number of students enrolled within the semester. RowCount function returns a
scalar non – fractional value referring to the number of rows upon execution of the query. Count

Topic: DDL and DML

Test III: Coding (3pts each number)
Directions: Write the SQL DDL or DML that would satisfy the problem or request that follows. Follow the casing
given for the database, entities and columns.

1. Create a database object named dboACTechniques.

2. Use the created database.
3. Create a table for all users and name it as User based on the given specification below.
FIELD DATA TYPE LENGTH
USERNAME VARCHAR 15
PASSWORD VARCHAR 25
FIRSTNAME CHAR 30
MIDDLENAME CHAR 30
LASTNAME CHAR 30
USERTYPEID VARCHAR 10
4. Modify the table column USERNAME such that it would not accept null values.
5. Set USERNAME as your primary key for User table and name it as pk_Username.
6. Create a table for UserType basing from the given specification that follows.

FIELD DATA TYPE LENGTH

USERTYPEID VARCHAR 10
USERTYPE CHAR 20

7. Modify the table column USERTYPEID such that it would not accept null values.
8. Set USERTYPEID as your primary key for User table and name it as pk_UserTypeID.
9. Set USERTYPEID as of User table as foreign key which references USERTYPEID of UserType table.
Name your constraint as fk_UserTypeID.

10. – 12. Insert the following values for the table User.

USERNAME PASSWORD FIRSTNAME MIDDLENAME LASTNAME USERTYPEID

JG-667 firSt.aiM44% JERISSE MENDEZ GOC-ONG 0001
WA-668 Fly.hiGher& WHISKY YOU ALDAN 0001
AM-669 Just.82411@ ANGELBERT HAIL MAGHANO 0002

13. Alter the table for User such that it will not allow null values to be inserted for column firstname.
14. Change the password of Jerisse to “rem3mber.13”.
15. Modify the user type id of WA-668 to 002.
16. Change the lastname of AM-669 user to BALDE.
17. Alter the table for User such that it will not allow null values to be inserted for column lastname.
18. Display all entries from both user and usertype in a single query string.
19. Delete entry of Whisky You Aldan.
20. Alter the table for User such that it will not allow null values to be inserted for column middlename.
"He, who hurries and leave people behind, would only remain waiting – sometimes astray. But whosoever
gives time and listen for others will never be judged alone – not in a day.”
- Moonlight Buddy