CAT

ACADEMIC YEAR: 2022-2023

LEVEL or CLASS: Year 1 A, B&C
TERM: II
COURSE TITLE: BASICS OF DATABASE DEVELOPMENT
COURSE CODE: SPEDD302
NUMBER OF TEACHING HOURS/WEEK: 2
DATE: 22/02/2023
DURATION: 90Minutes
MAXIMUM MARKS: 40

INSTRUCTIONS:

- The Exam has Ten(10) questions and all questions are compulsory
- Please read all questions carefully and make sure you understand the facts before you
begin answering
- Leave a left margin of at least one inch on every page of your answers.
- Good luck

Instructor: Mr. NZEYIMANA Celestin

Page 1 of 5
 Basics of Database Development
1. a. What is a data dictionary? (2 marks)
b. What is the role of a data dictionary? (2 marks)
a. A data dictionary is a document or file that contains detailed information about the
data used in a database or information system. It provides a description of the data
elements, their meanings, relationships, and usage within the system.
b. Data Dictionaries are useful for a number of reasons. In short, they:
 Assist in avoiding data inconsistencies across a project
 Help define conventions that are to be used across a project
 Provide consistency in the collection and use of data across multiple members
of a research team
 Make data easier to analyze
 Enforce the use of Data Standards

2. For enforcing data integrity, DBMS uses constraint.

a. What is a constraint in a database? (1 mark)
b. Give and explain 6 constraints found in MySQL? (3 marks)
a. A Database constraint is a rule enforced by the database manager to limit the
values that can be inserted, deleted, or updated in a table.
b. List of constraint
 Primary key constraint (to enforce existence integrity)
 Foreign key constraint (to enforce foreign key or referential integrity)
 Unique constraints (to enforce candidate key integrity)
 Check constraint (to restrict a column’s values; partial enforcement of
domain integrity)
 Not null
 Default

3. DELETE and TRUNCATE can be used to delete data in a table, explain the difference
between those two commands. (3 marks)
DELETE:

 The DELETE command removes specific rows from a table based on a

condition or criteria defined in the WHERE clause.
 The deleted data can be rolled back if a transaction is used.
 The deleted rows can be recovered using the backup file.

TRUNCATE:
 The TRUNCATE command removes all rows from a table.
 TRUNCATE is a DDL (Data Definition Language) command and can't
be rolled back.
 TRUNCATE resets the auto-increment value of the table to 1.
 TRUNCATE is faster than DELETE because it doesn't log individual
Page 2 of 5
 row deletions and frees up the table space in one go.

4. Data definition language (DDL) describes the portion of SQL that creates, alters, and
deletes database objects. These database objects include schemas, tables, views,
sequences, catalogs, indexes, variables, masks, permissions, and aliases.
a. Provide a syntax for adding a new column to the created table? (2 marks)
b. Give the syntax for changing the property such as column name, datatype, and
defined storage size of an existing column? (2 marks)
a. ALTER TABLE user ADD lastname VARCHAR(100) NOT NULL;
b. ALTER TABLE TableName MODIFY COLUMN ColumnName
Data_Type;
or
ALTER TABLE TableName CHANGE oldColumnName
NewColumnName Data_Type;

5. Explain the role of the SQL command below: (2 marks)

This will display all tables that are in shopping databases

6. Explain the following SQL command: (2 marks)

MySql>ALTER USER 'rca'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd’;
This command will change the password of rca user at localhost

7. Considering the below customer table:

a. Write an SQL query to rename the “home_address” column to ‘City’ the with default
value of “KIGALI”. (2 marks)
b. Write an SQL query to insert a new customer with the following records(First name:
KALISA, Last name: Callixte, email: kali@gmail.com, unknown birthdate, city: Nyanza,
Phone: 0788777286 and postcode: 20221) (3 marks)
c. Write a query to update the information of “Kamali”, change his Last name to “Ismael”
and his email to kamali@yahoo.com. (2 marks)
a. ALTER TABLE customer CHANGE home_address City varchar(10) DEFAULT

Page 3 of 5
 ‘KIGALI’; (2 marks)
b. INSERT INTO Customer (Firstname,Lastname,email,city,tel,postcode)
VALUES
(‘KALISA’, ‘Callixte’, ‘kali@gmail.com’, ‘nyanza’, ‘0788777286’, ‘20221’);
(3marks)

c. UPDATE TABLE customer set lastname= ‘Ismael’, email= ‘kamali@gmail.com’

WHERE cust_id=2; (2 marks)

8. After creating the user to MySQL, you may also need to grant appropriate privileges to
the user. Explain at least any 4 permissions that can be granted to MySQL users.
(2marks)
List of other common possible permissions that users can enjoy.
 ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full
access to a designated database (or if no database is selected, global access
across the system)
 CREATE- allows them to create new tables or databases or other objects
 DROP- allows them to them to delete tables or databases
 DELETE- allows them to delete rows from tables
 INSERT- allows them to insert rows into tables
 SELECT- allows them to use the SELECT command to read through databases
 UPDATE- allow them to update table rows

9. Consider the following data retrieved from a table called “user”:

a. Write an SQL query to retrieve all records from the “user” table. (2 marks)
b. Write an SQL query to delete 4th record from the “user” table. (2 marks)
a. SELECT * FROM user;
b. DELETE FROM user WHERE user_id=4;

10. Considering that you have been hired by XYZ Ltd company to the position of a database
administrator, then after, the CEO requested you to create a database for a stock
management system that typically involves several tables to store information about the
inventory and manage the flow of goods in and out of the stock. Below are a few listed
tables among others:

Page 4 of 5
  Products: This table stores information about the products, and has the following
attributes: product name, product_ID, description, and quantity.
Note: product_ID is a primary key
 Customers: This table stores information about the customers, this table has the
following attributes: customer name, customer_ID, tel, location, and email
Note: customer_ID is a primary key, tel, and email are unique, and by default, the
location is “NYAMIRAMBO”
 Sales Orders: This table stores information about the sales orders, this table has
the following attributes: order_number, order date, customer_ID, product_ID, and
quantity_ordered.
Note: order_number is a primary key, customer_ID and Product_ID are foreign
keys
a. Write an SQL query to create a database called “storedb” (2 marks)
b. Write an SQL query to create the above tables (Products, customers, and
Sales_orders) with their respective attributes, constraints, and other relevant
properties. (6 marks)
a. CREATE DATABASE storedb; (2 marks)

b. CREATING TABLES
- CREATE TABLE products (product_name VARCHAR(10), product_ID INT
PRIMARY KEY, description VARCHAR(10), quantity VARCHAR(10)); (2 marks)

- CREATE TABLE customers (customer_name VARCHAR(10), customer_ID INT

PRIMARY KEY, tel VARCHAR(10) UNIQUE, location VARCHAR(10) DEFAULT
‘NTAMIRAMBO’, email VARCHAR(10) UNIQUE); (2 marks)

- CREATE TABLE sales_order (order_number INT PRIMARY KEY, order_date

DATE, customer_ID INT, product_ID INT, quantity_ordered INT (2), FOREIGN
KEY(customer_ID) REFERENCES customers(customer_ID), FOREIGN
KEY(product_ID) REFERENCES products(product_ID)); (2 marks)

Good Luck

Page 5 of 5