TOPIC 2 - Databases

Question 1

what are the 2 ways to store data digitally?

Answer 1

flat files (one large table)
relational database (many smaller tables)

Question 2

benefits of a flat file

Answer 2

simple to create
easy to understand
all data in one place
good for one person with small amount of data

Question 3

key question of flat files

Answer 3

how are the needs of storing data for one person diff from needs of storing data for a large company

Question 4

goal of flat files

Answer 4

accurate, timely, relevant information

Question 5

limitations of flat files

Answer 5

lack of security
lack of multiple access
lack of data integrity
lack of scalability
program data dependance
lack of custom formats (view)

Question 6

what is a database?

Answer 6

a collection of related information stored in a structured form

Question 7

what does schema describe

Answer 7

structure of a database

Question 8

what is a database management system

Answer 8

a collection of programs that manipulate a database: updates, queries, etc

Question 9

why use databases and dbms?

Answer 9

Data integrity -> reduce redundancy and inconsistency
Data independence from the program
Security
Enable data sharing and high availability
Provide centralized data administration
Reduce application development time because standard software packages exist in the market for these tasks

Question 10

name the 4 common types of databases

Answer 10

hierarchy, network, relational, object oriented

Question 11

explain a hierarchy model

Answer 11

model data as a tree

Captures relationship among the data (parent can have many children)

Question 12

explain a network model

Answer 12

model data as a network
A child can have multiple parents
Faster than relational
Not as common

Question 13

explain object oriented model

Answer 13

model data as objects
Stores both the type of data and procedures that manipulate the data
Becoming more popular

Question 14

explain inheritance vs polymorphism

Answer 14

Inheritance is when an instance of a sub-class receives the same properties of its super-class

Polymorphism is when a sub-class implements a method which can override the one defined in the super-class

Question 15

explain relational model

Answer 15

modelled as a table and information is related

Question 16

what is an attribute/field

Answer 16

single value of a column (headers)

Question 17

what is a domain?

Answer 17

set of allowed values for an attribute

Question 18

what are entities?

Answer 18

a table stores information about entities (rows in a table)

people, places, things, or events

Question 19

what is a record/tuple

Answer 19

a collection of attribute values for an entitity

Question 20

what is a primary key

Answer 20

A minimum set of attributes whose values are unique in each row of a table

Question 21

what is a composite key?

Answer 21

when it takes 2 or more attributes to uniquely identify a row

Question 22

what are candidate keys?

Answer 22

when there is more than one key that can be used as a primary key and one is designated as the primary key

Question 23

what is a foreign key?

Answer 23

An attribute in a table that is also a primary key in another table and is used to link different tables together

Question 24

what do you do if your database is growing so large that you need to split it over multiple hard drives?

Answer 24

Separated how the data is stored (physical schema from how the data is used (exetrnal schema)

Question 25

what do you do if different users are interested in different parts of the database?

Answer 25

Create a single global view of the data (conceptual schema) that feeds into many individual views of the data (external schema) for the different user groups

Question 26

what are the 3 schema architecture?

Answer 26

external, conceptual, physical

Question 27

explain external schema

Answer 27

How the data is displayed to a particular user Different views for different user groups The rest of the database is hidden from that user

Question 28

explain conceptual schema

Answer 28

A global description of the whole database | Unbiased towards any particular group of users

Question 29

explain physical schema

Answer 29

How the data is physically stored and organized | What data is in which file on which disk

Question 30

why have 3 layers or schema?

Answer 30

data independence: separation of storage, organization and presentation

Question 31

what is the motivation of 3 layers for schema?

Answer 31

change the data format without changing the software

Question 32

what is SQL?

Answer 32

structured query language | Most commonly used language to create, manage, and query a database

Question 33

list the main operations of SQL

Answer 33

select, join, project

Question 34

explain data manipulation and definition

Answer 34

manipulation: the contents of a database can be accessed using a data manipulation langage which specifies the contents to extract definition: database contents must be clearly defined using a data defn language which specifies the type of each attribute/field/column heading

Question 35

what are the limitations of relational databases

Answer 35

multimedia data arrays of data unstructured text hierarchical data

Question 36

what are the criteria for a good database design

Answer 36

Correctness: info is accurate Completeness: it characterizes all the data Minimum redundancy: unfortunately this cannot be completely elimninated in all cases

Question 37

what are the 3 steps in database design for relational databases

Answer 37

1. Identify the entities to store and the relationships between these entities 2. Convert the ER diagram into tables 3. Fine tune your design

Question 38

what are the 3 cardinalities of relationships? Where do you put the primary key?

Answer 38

1: 1 relationships: place the primary key from one entity and any attributes of that relationship into the other entity’s table 1: N relationships: place the primary key from the “1” side of the relationship to the “N” entity’s table N:M relationships: create a new table with composite key (primary keys from both entities) and include associated data

Question 39

how are relationships represented in a tablle?

Answer 39

foreign keys in one entity's table | get their own table

Question 40

main purpose of normalization process?

Answer 40

minimize redundancy

Question 41

explain Boyce-Codd Normal Form

Answer 41

every attribute for an entity depends only on the candidate key(s)

Question 42

explain functional dependency A -> (B,C)

Answer 42

The value of A determines the values of B and C Knowing the value of A, there is only one possible value for B and C in the database

Question 43

Abilities of a relational database

Answer 43

``` Record data Search for an item Select and project Join Summarize ```

Question 44

Limitations of a relational database

Answer 44

Statistical analysis: what if, forecasting, correlation | Discover relationships, patterns, trends