Database Flashcards
(29 cards)
What is a colum
A column is a field that has a single piece of data stored about an entity
What is a row
A row is a record that is a complete set of data (fields) about an individual entity
What is a table
Table or file is all of the known individual entities of the entity type
What is a key field
It uniquely identifies a record / individual entity’s
Forms relationships
What is a primary key
It’s a unique identifier for each individual record
What is a foreign key
This is a primary key from another table in order to form relationships
What is a flat file database
One single table where all the data is recorded for every transaction
What are the problems with a Flat File Database
1: Unnecessary repetition of data
A) waste of resources e.g hardware space
B) take longer to complete: paying staff
2: Data inconsistency - The same piece of data has different values within the database
A) incorrect data
B) lose records/ error in processing
What is a Relational Database
Each table stores data about one entity and are limited together through relationships which are formed by primary and foreign keys
Advantages of a Relational Database
1: No data redundancy: unnecessary repetition of data
- each entities data is written once in the relevant table
- Use key field to refer to it
2: Improved data consistency: each piece of data is the same throughout entire database
What is a composite key
Primary key that consists of more than 1 field. Some or all of those field may be primary keys in another table
What is an attribute
It is a single piece of data for an entity e.g field name ‘member id’
What is an entity?
It is an object which information is stored e.g student
What is the Logical Data Model
It is in the design stage
It identifies the entities and the relationships between them using keys
It identifies attributes
It doesn’t describe the physical structure
What is the Physical Data Model
It is described from the logical data model
Produced during implementation stage
It creates the table structures such as
-Column Names
-Data type of each field
- Validation rules
The physical implementation differs depending on the actual database management system used
What is unnormalised data
It is a list of all the data fields much like a flat file database
Explain what 1NF, 2NF and 3NF are
1NF - A table is in 1Nf if all attributes are atomic and there are no repeating fields
2NF - A table is in 2NF ( and only if) it is in 1NF and all non key attributes are depends if in whole primary key
3NF - A table is in 3NF (and only if)it is 2NF and there are no functional dependencies between non key fields
How do you normalize data
1NF - Remove All repeating fields to new table with a copy of the private key
2NF - Fields that are partially dependent on primary key
Part of the primary key which they are dependent
3NF - Fields with a dependency on any non key field
The non key field on which they are dependent
What are the advantages and disadvantages of normalization
Advantages
- More efficient due to less redundancy (less hard disk>more storage space)
- More efficient better data structure, faster processing quicker retrieval of information
- Less redundancy means less inconsistency in data because data will only be entered once
- Data integrity is improved
Disadvantage
- Complex process to create database structure
- Generates more tables than de-normalized tables can lower efficiency
- Complexity, hard to use the database
What is referential integrity
Referential Integrity- Cannot enter a value for a foreign key field, if that value is not present in the referenced table (eg cannot add invoice in the invoice table linked to customer no 47 if there isn’t a number 47 in customer table)
A primary value cannot be changed or deleted if it is referred to I.e. the same value is listed on a foreign key in another table
What is a Data Dictionary
It is a list of all of the fields in the database contains all of the ‘meta data’ about each field e.g
Field name
Validation rules
Data type
What is the purpose of a Data dictionary
A data dictionary is useful when lots of people are working on the same system, we have consistent naming throughout the database
E.g. Foreign key in one table used the same name as the primary key in its linked field
What is the SQL code for creating a table
CREATE TABLE table_name (col1, col2)
What is the SQL code for retrieve (select)
SELECT Field
FROM table name
WHERE criteria (search criteria)
ORDER BY col1 ASC
Ascending* SELECT author title FROM Books WHERE category = “Science” ORDER BY author DESC
