Database

Question 1

What are functional dependencies?

Answer 1

These are database rules that help us group data in a database based on the relationship between them, It helps us find other pieces of data, using another piece of data.
Basically, a functional dependency is typically a relationship between a primary key and other none-keys on a table.

Question 2

What is redundancy in a Database?

Answer 2

It is a phenomenon that occurs when the same piece of identical data in a database occurs multiple times within that database. This could be empirical or atomic data, attributes etc.

Question 3

What are the existing Database anomalies? Explain them with detailed examples.

Answer 3

Acronym: DUI
1. Deletion anomaly:
This is a database anomaly that occurs when deleting a record causes and unintentional loss of some other data in the database.
2. Update anomaly:
This is a database anomaly that occurs when information in the database is updated wrongly or when we have to change information in the database to the correct information.
3. Insertion anomaly:
This is a database anomaly that occurs when inserting a new record into the database is not possible without also inserting some piece of unrelated data.

Question 4

How can we avoid database anomalies?

Answer 4

This can be done by normalizing all the tables in the database, and this can only be done by understanding functional dependencies, as functional dependences ensure every attribute on a table truly belongs to that table, hence helping us to reduce anomalies and redundancy in our database tables.

Question 4

What is a Determiner and A dependent in a functional dependency relationship? Explain with examples.

Answer 4

The determiner is the attribute on a table that maps on to another attribute(dependent) on the table. For example, a primary key, maps on to the none-keys in a table, meaning the none-keys depend on the primary key.

Question 5

Explain Full, Partial, and Transitive Dependencies, each with an example.

Answer 5

-> Full dependency occurs when an attribute on a table entirely depends another attribute on the table usually the primary key, this means that using one attribute (the pk) we can find the information about the dependent attribute with absolute certainty
-> Partial dependency occurs when an attribute on a table is dependent on one part of a coposite key (usually a composite key)
-> Transitive dependency occurs when one none-key attribute depends on another none-key attribute.

Question 6

What the different normal forms that exists in database?

Answer 6

The first to 5th normal form and The Boyce Codd normal form.

Question 7

Explain the first to third normal form.

Answer 7

1NF:
This normal form makes sures that each attribute in a table contains just 1 value
2NF:
This normal form ensures that every attribute in a table is fully dependent on the primary key.
3NF:
This normal aims at eliminating transitive dependences, by making sure that no none key depends on another none-key.

Question 8

What are database constraints? Explain with an example.

Answer 8

Database constraints are the rules in the database that ensure that data being stored in the database satisfies certain conditions

Question 9

Name the database constraints that exist.

Answer 9

Acronym: DECREB
-> Domain Integrity constrain
-> Entity Integrity constraints
-> Referencial Integrity constraints
-> Enterprise Constraints
-> Business Rules
-> Cardinality and connectivity constraints

Question 10

What is the Domain integrity constraint? Please give an example using a table.

Answer 10

The Domain Integrity Constraints are rules that ensure that the data being stored in a column or attribute of a table in the database is valid, meaning it is of the same data type as the attribute, it should follow the specified format, and it should follow any specified constraints put in place.

Question 11

What is an entity Integrity constraint? Please give an example using a table

Answer 11

This is a database rule that ensures that every table in a database contains a primary key attribute and every entry under said primary key attribute should be unique and cannot be null.

Question 12

What is the referential integrity constraint?

Answer 12

This is a data database rule that ensures that every foreign key in one table should have a corresponding primary key. This is done to avoid the occurrence of any orphan records.

Question 13

What is an orphan record?

Answer 13

It is a record that contains a foreign key, whose corresponding primary does not exist.

Question 14

What are enterprise or semantics constraints?

Answer 14

These are rules in the database that are specified by the database user or the database administrator e.g The entity “TEACHER” can not teach more than 3 courses(Its Course attribute cannot hold and integer value greater than 3) .

Question 15

What are business rules or constraints?

Answer 15

These are the rules that ensure that every requirement of the database user is fulfilled and present in the database.

Question 16

What is the Cardinality and connectivity constraint?

Answer 16

Cardinality defines the relationship between tables in a database, it tells us how many instances of one entity relate to how many instances of another table. its types include one-to-one, one-to-many, and many-to-one.

Connectivity defines the maximum and minimum number of instances of an entity that can relate to the maximum or minimum number of instances of another entity. Its types include mandatory and optional relationships.

Question 17

State and explain the types of database relationships.

Answer 17

1. Identifying and Non-Identifying Relationships:
   An Identifying relationship is one where the primary key contains the foreign key while a non-identifying relationship is one where the primary key does not contain the foreign key.
2. Mandatory and Optional Relationships
   Mandatory relationships refer to the relationship between entities in a database where for one entity to exist the other entity must also exist.
   Optional relationships refer to the relationship between entities in a database where entities are related but the existence of one entity does not require the existence of another.

Question 18

How can Databases be classified?

Answer 18

Model, Number of users, and distribution.

Question 19

Classify databases based on the following criteria: data model, number of users, and distribution.

Answer 19

Based on the data model:

-> Relational Model: This is the most used database data model, databases that use this model store data in rows and columns within tables and store this data based on the relationship between said tables. ex: Mysql, SQLI, Oracle, DB2.
->Heirachical model: Databases that use this model organize their data in a tree-like structure with parent and child data relationships.
-> Network data model: Databases that use this model store data in a network-like manner consisting of nodes with relationships between them called links
-> Object-Oriented Model: Databases that use this model, store data in the form of objects, similar to how objects are used in object-oriented programming. ex: O2, Object Store, Jasmine, JSON.

Based on the number of users:

->Single user: These databases support only a single user at a time.
-> MultiUser: These databases can support multiple users at a time.

Based on distribution:
-> Centralized databases: Here the DBMS and the Database are stored on the same site and other computers on different sites can access them.
-> Distributed database: Here the database and the DBMS software are stored in a distributed system between several different computers or servers.

Question 20

What are the types of Distributed databases that exist?

Answer 20

-> Homogenous: Here every site using the distributed database, uses the same DBMS software.
-> Heterogenous: Here note every server or site using the the distributed database uses the DBMS.

Question 21

Describe the physical Implementation of data in a database and highlight the key considerations

Answer 21

It is a process that involves converting a logical data model into a physical data structure that can be stored in a computer. The key considerations at this stage involve:
Acronym: SIP
Storage Format:
It involves deciding whether to use a row-based or column base storage format, depending on query performance and disk space efficiency

Indexing:
Deciding how to index data, for fast data retrieval, through the creation of efficient access paths

Partitioning:
Dividing large tables into smaller manageable tables to improve efficiency.

Question 22

Explain how files can be structured and indexed.

Answer 22

File structure:
This is how data is organized within files on a disk. e.g Heap and Clustered files

Index structure:
It is the data structure that improves the speed of data retrieval operations. e. g B-Trees(range queries) and Hash indexes(equality searches)

Question 23

Explain how concurrent access is controlled in a database.

Answer 23

Concurrent access is done to multiple transactions work concurrently without causing any data inconsistencies. It is archived through
Acronym => TIL
-> Time stamp-based protocol:
Using timestamps to order transactions and resolve conflicts

-> Isolation Levels:
Isolating transactions from the effect of other transactions

-> Locking mechanism:
Using locks to control access to data.

Question 24

What is breakdown resistance?

Answer 24

It is the level to which a database is resistant to hardware or software failures to maintain continuous operation

Question 25

What is the importance of security in a database system and identify the potential security threats to a database.

Answer 25

The importance of database security is to protect the integrity, confidentiality, and availability of the data stored in a database. Potential security threats involve:
Acronym: We Unanimously Decided Michael Solie

Weak Authorization and Authentication: weak authentication and access control measures can lead to individuals gaining unauthorized access to the database.

Unauthorized Access: unauthorized access into the database will lead to viewing, stealing, or modifying of sensitive data

Denial of Services: Attackers can overwhelm the database with requests, preventing legitimate users from accessing the database

Malware: malicious software can infect the database system leading to the corruption, encryption, or deletion of data.

SQL Injection: Attackers can exploit vulnerabilities in web applications to insert malicious SQL code into the database.

Question 26

What is SQL?

Answer 26

It is a programming language that is used to manage and perform operations on data in a database.

Question 27

What does DDL stand for and what is its purpose?

Answer 27

Data Definition Language. It is used to define tables in a database.

Question 28

What does DML stand for and what is its purpose?

Answer 28

Data Manipulation Language. It is used to manipulate data in tables

Question 29

What does DQL stand for and what is its purpose?

Answer 29

Data Query Language. It consists of only one command called SELECT. It is used to fetch a specific piece of data from the database.

Question 30

What does DCL stand for and what is its purpose?

Answer 30

Data Contol Language. It is used to grant or deny Access privileges.

Question 31

What does TCL stand for and what is its purpose?

Answer 31

Transaction Control Language. It is used to change the state of data, it includes commands like COMMIT and ROLLBACK