Compsci Is For Nerds Flashcards
Database
Collection of interrelated persistent data stores without unnecessary redundancies to serve one or more applications
DBMS
Database Management System enables users to create maintain and control access
DBMS Disadvantage
Complexity, cost of management, performance, converting old data
Rows and columns
Rows called records column given distinct nane
Must be unique to prevent redundancies
Field
Attribute within a record carrying data, makes primary key
Entity
A table, attributes are shown as column headings
Must have primary key
Things that can only have one instance cannot be entities
Candidate Key
Minimal number of attributes uniquely identifting occurence of an entity
Primary key is chosen candidate key to identify records
Attribute lore
If max amount of values is known it can be an attribute, if an attribute has its own attribute it should be a seperate entity referenced by ID
Weak entity vs Strong entity
Weak entity is a child as one box and strong entity is a parent as box with outline
1NF Check
A table must not have more than one entry in any field
1NF Fix
Duplicate the rest of the row in the offending field
Non 1NF problems
Slow search
Inserting deleting updating difficult and error prone
Wasted space
Key Field (Candidate Key)
Different for every row so can be used to identify each row
Fields may need to be combined to form a key
Dependent key
If we know the value of a record from other records it is dependent
B is dependent on A
A: Determinant
B: Dependent
Non key fields are dependent on key fields
2NF Check
Check in 1NF
All non key fields must depend on whole table key
2NF Fix
Two or more tables created to replace original table
Non 2NF Problems
Information is duplicated
Null values may be required
Insertion, updating, deletion problematic
3NF Check
Check in 2NF
All non key fields dependent on only the key
No transitive dependency
3NF Fix
Two or more tables must be created to replace original table
3NF Inconsistency
If more than one possible candidate key, which is composite and the keys share a common field
BCNF Check
Check in 3NF
Check all determinants in a relation must be candidate keys
BCNF Fix
The fields that depend on the field which is not a candidate key must be removed and put into a new table with their determinants as key
What do fourth and fifth normal forms deal with
3 or more key fields and no dependent fields
4NF Check
Check in BCNF
No independent multi-value dependencies
4NF Fix
Split the table so there is no more than one MVD in each of the new tables
3 way multi dependency into 2 two way dependencies
5NF Check
Check in BCNF
Check table without any related multi-valued dependencies
Related Multi Value Dependencies
MVD not totally independent of each other
Not all possible combinations occur in the data
5NF Fix
Split table into 3 tables so that there is no more than one MVD in each of the new tables
3 way multiple dependency broken down to 3 2 way dependencies
DBMS Needs
DDL - Data Definition Language
DML - Data Manipulation Language
DDL
User can create new objects with create alter and drop statements
DDL Syntax
Create Table table-name (column-details)
Drop Table table-name
Alter Table table-name command column-details
Column details
Record name with data type
E.g ID_Num INTEGER,…
Column types
Char, Varchar, Integer, Numeric, Boolean, Date
Type info
Char is fixed while varchar is variable
Numeric(n,m) where n and m are digits after decimal
Data Manipulation Language
User can retrieve, insert, delete and update data in a database
DML Insert
Insert Into table-name (columns)
Values (value-list)
Can name columns where any unmentioned columns are set to null
Can copy data using select
DML Update
Update table-name
Set column-assignments
Where conditions
Column assisgnments
List of assignments separated by commas of form:
column-name = new-value
DML Delete
Delete From table-name
Where conditions
View Syntax
Create View view-name As
Select (x) from emp/dept etc.
Can make view based on another view
Why create a view
Restrict access
Save complex expressions
Sort/group information
Make Logical Data Independence
