Database Flashcards
What are functional dependencies?
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.
What is redundancy in a Database?
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.
What are the existing Database anomalies? Explain them with detailed examples.
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.
How can we avoid database anomalies?
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.
What is a Determiner and A dependent in a functional dependency relationship? Explain with examples.
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.
Explain Full, Partial, and Transitive Dependencies, each with an example.
-> 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.
What the different normal forms that exists in database?
The first to 5th normal form and The Boyce Codd normal form.
Explain the first to third normal form.
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.
What are database constraints? Explain with an example.
Database constraints are the rules in the database that ensure that data being stored in the database satisfies certain conditions
Name the database constraints that exist.
Acronym: DECREB
-> Domain Integrity constrain
-> Entity Integrity constraints
-> Referencial Integrity constraints
-> Enterprise Constraints
-> Business Rules
-> Cardinality and connectivity constraints
What is the Domain integrity constraint? Please give an example using a table.
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.
What is an entity Integrity constraint? Please give an example using a table
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.
What is the referential integrity constraint?
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.
What is an orphan record?
It is a record that contains a foreign key, whose corresponding primary does not exist.
What are enterprise or semantics constraints?
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) .
What are business rules or constraints?
These are the rules that ensure that every requirement of the database user is fulfilled and present in the database.
What is the Cardinality and connectivity constraint?
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.
State and explain the types of database relationships.
- 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. - 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.
How can Databases be classified?
Model, Number of users, and distribution.
Classify databases based on the following criteria: data model, number of users, and distribution.
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.
What are the types of Distributed databases that exist?
-> 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.
Describe the physical Implementation of data in a database and highlight the key considerations
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.
Explain how files can be structured and indexed.
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)
Explain how concurrent access is controlled in a database.
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.
