Compsci Is For Nerds

Question 1

Database

Answer 1

Collection of interrelated persistent data stores without unnecessary redundancies to serve one or more applications

Question 2

DBMS

Answer 2

Database Management System enables users to create maintain and control access

Question 3

DBMS Disadvantage

Answer 3

Complexity, cost of management, performance, converting old data

Question 4

Rows and columns

Answer 4

Rows called records column given distinct nane

Must be unique to prevent redundancies

Question 5

Field

Answer 5

Attribute within a record carrying data, makes primary key

Question 6

Entity

Answer 6

A table, attributes are shown as column headings
Must have primary key
Things that can only have one instance cannot be entities

Question 7

Candidate Key

Answer 7

Minimal number of attributes uniquely identifting occurence of an entity
Primary key is chosen candidate key to identify records

Question 8

Attribute lore

Answer 8

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

Question 9

Weak entity vs Strong entity

Answer 9

Weak entity is a child as one box and strong entity is a parent as box with outline

Question 10

1NF Check

Answer 10

A table must not have more than one entry in any field

Question 11

1NF Fix

Answer 11

Duplicate the rest of the row in the offending field

Question 12

Non 1NF problems

Answer 12

Slow search
Inserting deleting updating difficult and error prone
Wasted space

Question 13

Key Field (Candidate Key)

Answer 13

Different for every row so can be used to identify each row

Fields may need to be combined to form a key

Question 14

Dependent key

Answer 14

If we know the value of a record from other records it is dependent

Question 15

B is dependent on A

Answer 15

A: Determinant
B: Dependent
Non key fields are dependent on key fields

Question 16

2NF Check

Answer 16

Check in 1NF

All non key fields must depend on whole table key

Question 17

2NF Fix

Answer 17

Two or more tables created to replace original table

Question 18

Non 2NF Problems

Answer 18

Information is duplicated
Null values may be required
Insertion, updating, deletion problematic

Question 19

3NF Check

Answer 19

Check in 2NF
All non key fields dependent on only the key
No transitive dependency

Question 20

3NF Fix

Answer 20

Two or more tables must be created to replace original table

Question 21

3NF Inconsistency

Answer 21

If more than one possible candidate key, which is composite and the keys share a common field

Question 22

BCNF Check

Answer 22

Check in 3NF

Check all determinants in a relation must be candidate keys

Question 23

BCNF Fix

Answer 23

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

Question 24

What do fourth and fifth normal forms deal with

Answer 24

3 or more key fields and no dependent fields

Question 25

4NF Check

Answer 25

Check in BCNF | No independent multi-value dependencies

Question 26

4NF Fix

Answer 26

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

Question 27

5NF Check

Answer 27

Check in BCNF | Check table without any related multi-valued dependencies

Question 28

Related Multi Value Dependencies

Answer 28

MVD not totally independent of each other | Not all possible combinations occur in the data

Question 29

5NF Fix

Answer 29

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

Question 30

DBMS Needs

Answer 30

DDL - Data Definition Language | DML - Data Manipulation Language

Question 31

DDL

Answer 31

User can create new objects with create alter and drop statements

Question 32

DDL Syntax

Answer 32

Create Table table-name (column-details) Drop Table table-name Alter Table table-name command column-details

Question 33

Column details

Answer 33

Record name with data type | E.g ID_Num INTEGER,...

Question 34

Column types

Answer 34

Char, Varchar, Integer, Numeric, Boolean, Date

Question 35

Type info

Answer 35

Char is fixed while varchar is variable | Numeric(n,m) where n and m are digits after decimal

Question 36

Data Manipulation Language

Answer 36

User can retrieve, insert, delete and update data in a database

Question 37

DML Insert

Answer 37

Insert Into table-name (columns) Values (value-list) Can name columns where any unmentioned columns are set to null Can copy data using select

Question 38

DML Update

Answer 38

Update table-name Set column-assignments Where conditions

Question 39

Column assisgnments

Answer 39

List of assignments separated by commas of form: | column-name = new-value

Question 40

DML Delete

Answer 40

Delete From table-name | Where conditions

Question 41

View Syntax

Answer 41

Create View view-name As Select (x) from emp/dept etc. Can make view based on another view

Question 42

Why create a view

Answer 42

Restrict access Save complex expressions Sort/group information Make Logical Data Independence