Database Theory and Design Flashcards
What is a database?
An organised collection of related data.
What is a DBMS?
Software that manages &controls access
to the database
What is a database application?
A program that interacts with the database.
What are the disadvantages of file-based systems?
Data Duplication. Data dependence. Incompatible file formats. Fixed queries/proliferation of application programs.
What are the differences between Database Systems and File-based systems.
In the file-based approach, each application program defines and manages its own data.
While in the database approach, a shared database is stored to control the redundancy and ensure the data consistency and integrity.
In the file-based approach, each application program directly accesses the data.
While in the database approach, all applications access the database through DBMS which provides a few important access control facilities.
What is the three level architecture of a DB?
External - Describes what part of the database that is relevant to each user.
Conceptual - Describes what data is stored in the database and the relationships among the data.
Internal - Describes how the data is stored in the DB. Physical representation of the DB.
What is data independence?
Data independence is the capacity that upper levels are unaffected by changes to lowerlevel.
What are the two kinds of data independence?
Physical and Logical. Physical/Logical is the ability to make changes to the physical storage structures/logical structure without affecting application programs that access the data.
Which model is the most popular logical data model?
The Entity-Relationship Model.
What is Database Scheme and Database State?
The database schema is the design of the database. It represents the organisation of data and the relationships between different elements. The database state represents the actual data stored in the database in a particular point in time.
What are the 4 components of an er model?
Entity, Relationship, Attribute, Multiplicity Constraint.
What is a weak entity?
A weak entity is an entity in a database that does not have a primary key attribute of its own and is dependent on another entity.
What is aggregation?
An abstraction through which relationships are treated as higher-level entities.
What is a relation?
A 2d table that has specific characteristics.
What is Entity Integrity?
Entity Integrity ensures that row in a tuple is uniquely identified by a unique primary key.
What is Referential Integrity?
Referential Integrity ensures the consistency of relationships through foreign keys. Each foreign key refers to the primary key of another table.
What are Domain Constraints?
Domain Constraints ensure that values stored in a database conform to predefined data types and constraints.
What are the main objectives of normalisation?
Remove redundancy, and remove potential for insert, update and delete anomalies.
What is a functional dependency?
When the value of one attribute is determined by the value of another attribute.
What is a transitive dependency?
this occurs when a functional dependency indirectly influences another attribute through a chain of dependencies. For example if A is needed for B, and B is needed for C, then C has a transitive dependency on A.
What is a partial functional dependency?
when a non pk attribute is functionally dependent on a part, but not the whole, of a composite pk.
How does select work in relational algebra?
It selects all tuples that satisfy the condition from a relation.
How does projection work in relational algebra?
Produces a new relation with only some of the attributes from an old relation. Removes duplicate tuples.
How does rename work in relational algebra?
Renames a relation to another name.
How does Union and Intersection work in relational algebra?
Union produces a relation that includes all tuples in R1 or R2, or both R1 and R2. Intersection produces a relation of all the tuples in both R1 and R2. R1 and R2 must be union compatible.
How does Difference and Cartesian Product work in relational algebra?
Difference produces a relation that includes all tuples in R1 that are not in R2. R1 and R2 must be union compatible. Cartesian Product produces a relation that has the attributes of R1 and R2 and includes all possible combination of tuples.
How does Theta Join Equijoin and Natural Join work in relational algebra?
The Theta Join is a generic join operation that combines rows from two relations based on a specified condition that is not necessarily an equality condition. Equijoin is a version of theta join that uses an equality condition. Natural Join is a special case of Equijoin where the join condition is automatically determined by matching columns with the same name in the participating relations.
What is DDL?
Data Definition Language. The SQL code for defining the database structure and controlling access to the data.
What is DML?
Data Manipulation Language. Used for retrieving and updating data.
What does this do?
SELECT empno, ename, job, sal
FROM emp
WHERE hiredate BETWEEN ’01-JAN-81’ AND
’31-DEC-81’
AND job IN (’MANAGER’,’PRESIDENT’)
AND comm IS NULL
AND (sal>=2600 OR sal<=10000)
ORDER BY job, sal DESC;
Selects fields empno, ename, job and sal from the emp relation. Selects rows where the hiredate value is between 1 Jan 1981 and 31 Dec 1981
and the job is MANAGER or PRESIDENT, and comm is NULL and the sal is between 2600 and 10000. It then orders by job, then salary in descending order.
What does this do?
SELECT COUNT(CustomerID), Country
FROM Customers
WHERE Country LIKE ‘U%’
GROUP BY Country
HAVING COUNT(CustomerID)>5;
Selects the field Country and the Count of the field CustomerID from the Customers relation. Selects rows where the Country starts with a U. Then groups by the unique values in the Country column, and filters the grouped results to include only countries which have a count of customerID greater than 5.
What does this do?
SELECT *
FROM Products
WHERE SupplierID IN (SELECT SupplierID FROM Suppliers
WHERE Country=‘UK’)
Retrieves all fields from Products. Selects rows where the supplierID is in the result of the subquery which retrieves the supplierID field from the Suppliers relation and filters to rows where the country is the UK.
What does this do?
SELECT C.CustomerName, O.OrderID
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;
Selects the columns CustomerName and OrderID from Customers and Orders, and performs a left join on both tables based on CustomerID. Orders by the customer name.
How does insert work in SQL?
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
How does update work in SQL?
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
How does an Inner Join work?
Returns only the rows where there is a match in both tables based on the specified condition.
How does a Left Join work?
Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for columns from the right table.
How does a Right Join work?
Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for columns from the left table.
How does a Full Join work?
Returns all rows when there is a match in either the left or the right table. If no match is found, NULL values are returned for columns from the table without a match.
What does this do?
CREATE TABLE Products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName VARCHAR(100) NOT NULL,
categoryId INT,
CONSTRAINT fk_category FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
) ENGINE=INNODB;
Creates a table named Products. Defines the columns as: a primary key called productID which is an auto incremented integer, a productName which is a VARCHAR(100) and cant be null, and a categoryID which is an int. It then defines a constraint called fk_category which references the categoryID column with the categoryID column in the Categories table. It lastly defines the storage engine.
