from patrick Flashcards
a) Define the following terms as used in databases:
i) Entity [2 marks]
ii) Tuple [2 marks]
iii) Derived attribute [2 marks]
i) Entity: A real-world object or concept with distinct characteristics, stored as a record in a database.
ii) Tuple: A single row in a table representing a set of related data (a record).
iii) Derived attribute: An attribute whose value is calculated from other attributes in the database.
b) Give one example of a derived attribute [1 mark]
An example of a derived attribute is Age, which can be calculated from a person’s Date of Birth.
c) With the use of examples, explain the differences between the following:
i) Simple attribute VS single valued attribute [4 marks]
a simple attribute is one piece of data that cant be broken down further . its indivisible. e,g First name
while
single valued attribute contains one value for each record. e.g StudentID
c) With the use of examples, explain the differences between the following:
ii) Composite attribute VS multi-valued attribute [4 marks]
a composite is an attribute that can be divided into smaller parts. e.g full address can be broken int street , city , zip code
while
a multi attribute is an attribute that can have multiple values. e,g phone number can have can include home and mobile phone number.
d) Identify and explain in brief any two symbols used in the development of ER diagrams. [4 marks]
Rectangle(entity) - represents real world object or concept.
diamond(relationship) - represents a relationship between entities
with examples Explain the purposes of the following aggregate functions? [6 marks]
i. Max()
ii. Count()
iii. Avg()
Max() returns highest value in a column. e.g
SELECT MAX(Salary) FROM Employees
Count(). returns now of rows that match a condition e.g SELECT COUNT(*) FROM Students WHERE Course = ‘Computer Science’;
Avg() returns average value in a numeric column. SELECT AVG(Price) FROM Products;
a) Give any three examples of DBMSs. [3 marks]
Oracle database
MySQL
PostgreSQL
b) The basic data storage structure for a relational database is a relation. Give and explain any four properties of a relation. [8 marks]
c) With the help of an illustration, explain the three-layered architecture model for database design. [6 marks]
Tabular Format: Data is stored in tables with rows and columns, where each table represents a relation.
Unique Rows: Each row in a table must be unique; no two rows can have the same values in all columns.
Attribute Names: Columns (attributes) in a table have unique names, and each column contains data of a specific type.
Order Independence: The order of rows and columns does not affect the data; rows can be retrieved in any sequence.
d) Using an example, describe a unary relationship [2 marks]
draw
e) Explain THREE(3) advantages of database systems over file systems (paper-based)
[6 marks]
Data Integrity: Databases enforce rules to maintain accuracy and consistency of data, reducing errors compared to manual paper-based systems.
Efficiency: Databases support quick data retrieval and manipulation through queries, while file systems can be slower and more cumbersome to manage.
Concurrent Access: Databases allow multiple users to access and update data simultaneously, whereas paper-based systems are limited to single-user access.
a) Describe the five components of the DBMS environment and discuss how they relate to each other. [10 marks]
hard
b) Give and explain any 5 functions a DBMS can support [5 marks]
Data Storage: Stores and organizes data efficiently in tables or files.
Data Retrieval: Provides tools to query and retrieve specific data from the database. Data Manipulation: Allows users to insert, update, and delete data. Data Security: Controls access to data through user authentication and authorization. Backup and Recovery: Offers mechanisms to back up data and restore it in case of loss or corruption.
c) Within the context of databases, define a data model. [2 marks]
Collection of logical constructs used to represent data structure and relationships within the database
d) Give any two examples of data models. [2 marks]
hierarchial model. represented by an upside down tree. -adv conceptual simplicity , efficiency and data independence . cons complex implementation , lacks structural independence.
network db model. conceptual simplicty promotes db integrity.. lacks structural independence.
relational db. struectural independence, improved conceptual simplicity. dis poor design and implementation is made easy. may promote islands of info problems. Substantial hardware and system software overhead
e) Identify and explain in brief three concerns addressed by a relational data model. [6 marks]
dis
poor design and implementation is made easy.
may promote islands of info problems.
Substantial hardware and system software overhead
a) Distinguish between the Data Definition Language (DDL) and the Data Manipulation Language (DML) [2 marks]
ddl - statements that specify and modify db schemas e.d CREATE, Alter, DROP
while
DML - statments that manipulate db content. INSERT , UPDATE , DELETE
c) State Two (2) advantages of a Network database model [2 marks]
data independence
conceptual simplicity
e) Identify and explain in brief Three (3) concerns addressed by a relational data model. [6 marks]
c) Give and explain any Four (4) properties of a relation. [8 marks]
d) The student wrote a query to list the students who stay in Gaborone or Francistown as follows:
Select * from student where city = ‘Gaborone’ OR city = ‘Francistown’
Rewrite the above SQL statement using the IN operator.
SELECT * FROM student WHERE city IN (‘Gaborone’ , ‘Francistown’);
e) Tshepo wanted to design a database using object oriented approach. He has asked you to differentiate between an object and a class in object oriented data model. What is the difference between these two?
[4 marks]
an object is an instance of a class while class is a blueprint/template of an object
a class co
f) Identify and explain in brief any THREE objectives of the three level architecture [6 marks]
Consistent Data Access: All users access the same data.
View Independence: Changes in one view do not affect others.
Storage Abstraction: Users are unaware of physical data storage details.
a) Define the following terms as used in databases:
i) Record [2 marks]
ii) Primary key [2 marks]
iii) Entity integrity [2 marks]
Record: A single row in a table, representing a set of related data items about an entity.
ii) Primary Key: A unique identifier for each record in a table, ensuring no duplicate rows.
iii) Entity Integrity: A constraint ensuring that the primary key of a table is unique and not null, maintaining data accuracy and consistency.
c) Identify only one operator under the following groups and explain when each is used.
i. Comparison operator
ii. List operator
[4 marks]
List Operators: Used to check if a value matches any value in a list (e.g., IN, BETWEEN).
Comparison Operators: Used to compare values (e.g., =, <>, >, <, >=, <=).
Set Operators: Used to combine results from multiple queries (e.g., UNION, INTERSECT, EXCEPT).
Logical Operators: Used to combine multiple conditions (e.g., AND, OR, NOT).
do 3a
do 4a
d) Using an example, describe a unary relationship [2 marks]
