Databases Flashcards
What is a database?
A database is a collection of data
What is an entity?
An ententity is an item of interest about which information is stored
What is a relational database?
A realtional database is a database which recognises the differences between entities by creating different tables for each entity
What are attributes?
Attributes are characteristics of entities, categories about which data is collected
What is a flat file?
A flat file is a database that consists of a single file that is more likely to be based around one single entity and its attributes
What is a primary key?
A primary key is a unique identifier for each record in the table, shown by underlining it (i.e. DoctorID, CarID, etc.)
What is a foreign key?
A foreign key is the attribute that links two tables, acting as a primary key in one table and a foreign key in another (i.e. DoctorID will be a foreign key to the Patient table)
What is a secondary key?
Secondary key allows a database to be searched quickly by setting up a secondary index on an attribute that it’s easier to remember.
A secondary key is a key field which can be used to access a table in a different way.
What are the different degrees of relationships that 2 databases possess when related?
When relating 2 databases there are different degrees of relationships they can possess:
I. One to one;
II. One to many;
III. Many to many;
How do you create a table using SQL code ? (Not on Spec but useful)
CREATE TABLE employee
(
EmpID INTEGER NOT NULL PRIMARY KEY,
EmpName VARCHAR(20) NOT NULL,
HireDate DATE,
Salary CURRENCY
);
How do you insert a new record in a database?
INSERT INTO tableName (column1, column2)
VALUES (value1, value2)
How do you update a record in a table?
UPDATE tableName
SET column1=value1, column2=vallue2
WHERE columnX = value
How do you delete a record in a table?
DELETE FROM tableName
WHERE column=value
What does ACID stand for
Atomicity
Consistency
Isolation
Durability
What is data redundancy
When there is unnecessary data repetition in a database i.e. having different students studying the same subject
Cons that follow data redundancy are
An unnecessary amount of data storage capacity required to store duplicated data
An increased risk of having inaccurate data
It is more time consuming and problematic to update the database so that’s it’s accurate
Data that is inconsistent lacks
Integrity
Data that lacks integrity is
Inconsistent
Using an entity relationship tables have some of the following pros
Less data redundancy and less chance of error leading to inconsistency in the data and less time spent updating the data as it has to be done only in one place
Each table in a entity relationship database represents
An entity
A composite primary key
A composite or concatenated primary key is when more than one field is combined to create a unique primary key for a table.
Define Data integrity
Refers to the accuracy and reliability of data and is of prime importance in any computer system, as when data is inaccurate, information produced from systems will be discredited, hence why, validation and verification techniques are used to ensure data integrity
Referential integrity
Is the process of ensuring consistency. This ensures the information is not removed if it is required elsewhere in a linked database
What’s normalisation
The process of coming up with the best possible layout for a relational database.
Normalisation tries to accomplish
No redundancy
Consistent data through linked tables
Records can be added and removed without issues
Complex queries can be carried out
There are 3 types of normalisation
First Normal Form
Second Normal Form
Third Normal Form
First Normal Form
There must be no attribute that contains more than a single value
Second Normal Form
A database which doesn’t have partial dependencies and is in its NF1 can be said to be in is NF2. This means that no attributes can depend on par of a composite key
Third Normal Form
If the database is in NF2 and contains no non-key dependencies then it’s in NF3.
What’s a non key dependency
Means the attribute only depends on the value of the primary key and nothing else
What’s indexing
Indexing is a method used to store the position of each record ordered by a certain attribute
When selecting and managing data to reduce data input only…
Data that fits a certain criteria will be selected
What does SQL stands for
Structured Query Language
What’s one common way to exchange data
EDI as it doesn’t require human interaction and enables data transfer from one computer to another
What does EDI stands for
Electronic Data Interchange
The ORDER BY statement can order data in either
Ascending or descending order
To order by descending order syntax
ORDER BY … Desc
JOIN syntax explained
Provides a method of combining rows from multiple tables based on a common field between them
i.e. SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany
FROM Movie
JOIN Director
ON Movie.DirectorName = Director.DirectorName
Atomicity means
A transaction must be completed fully. Half completed change must not be saved back to the database
Consistency means
Any changes in the database must retain the overall state of the database
Isolation means
A transaction must not be interrupted by another transaction by locking values that are being accessed by one.
Durability means
Once a change has been made to a database it must not be lost due to a system failure
How do you write nested selects
SELECT name FROM student
WHERE grade > (SELECT grade FROM student
WHERE name=”Smith”);
How do you join two tables
SELECT student.name, course.name FROM student
JOIN course ON student.courseID=course.CourseID;
What is a transaction
A change to the database
What does DBMS stands for?
The Database Management System
The Database Management System ensures that…
when a transaction takes place, the database changes from one consistent state to another.
For example when a transaction is taking a place a record may be locked to prevent another transaction from interfering with the first transaction.
To get a table into Second Normal Form (2NF) you need to…
- Check data is already in 1NF
- Remove any partial dependencies, this means that one or more of the fields depends on only part of the primary key
- Fix any many-to-many relationships.
What is the term, ‘error diagnostics’ ?
These are often messages from the compiler to the programmer indicating the location of syntax or possible logical errors, these message often reference the line number where the problem has occurred.
Atomicity means ….
that a change in a database must be either completely performed or not performed at all.
What is an advantage of a flat file database over a relational database?
they are simple to set up and ideal for very small databases.
What are disadvantages of a flat file database over a relational database?
They are inefficient because they create data duplication which can also affect the integrity of data and therefore the accuracy of query results.
