Theory

Question 1

Desirable attributes of data

Answer 1

Sharable, Transportable, Accurate, Relevant, Timely and Secure

Question 2

Common features of a database

Answer 2

Storage medium, structure and rapid data entry & retrieval

Question 3

Problems with data management

Answer 3

Redundancy (same data stored in multiple places)
Poor interface (data difficult to access or understand)
Lack of reality (poor model of the real world)
Lack of data integration (data dispersed across different systems)
Delays (waiting to gather data)
Lack of data control (data poorly managed)

Question 4

What is hardness of information?

Answer 4

Hardness indicated the accuracy of the data. The softer the data, the more resources the manager needs in order to verify that the information is correct

Question 5

What is the richness of data?

Answer 5

Richness is related to the way the data is communicated. If the information gathered comes directly from the source (face-to-face), the data is rich. If it is communicated via another person over email, not so rich

Question 6

Types of information sought by manager during change

Answer 6

Goal setting information: information related to external environment changes and use that to set a new goal
Gap information: information showing the gap between where company stands now and goal they want to achieve
Change information: shows what happens when a particular change is made

Question 7

Definition entity

Answer 7

An entity is a table in a database

Question 8

Definition instance

Answer 8

An instance is a row in a table

Question 9

Definition primary key

Answer 9

The primary key is the unique identifier of an entity in a database

Question 10

Definition foreign key

Answer 10

The foreign key refers to the primary key in the other table of a relationship.

Question 11

How to create a table

Answer 11

CREATE <Table> (

<attribute> datatype
" (how many attributes you have)
PRIMARY KEY (<Name>));
</Name></attribute>

Question 12

Assign values to a table

Answer 12

INSERT INTO <Table>(
(<column>, <column>, etc.)
VALUES
(value1, value2, etc.)</column></column>

Question 13

SELECT * FROM <Table name>

Answer 13

Shows entire table selected

Question 14

SELECT * FROM <Table name> WHERE <column></column>

Answer 14

Shows rows where column condition is met

Question 15

WHERE + AND or OR

Answer 15

Where can be combined with AND or OR to allow for multiple conditions on multiple columns

Question 16

ORDER BY

Answer 16

Order by can be used to display the data in a specific order. Automatically does so in ascending (ASC) order, but when DESC is typed, it does so in descending order

Question 17

UPDATE <Table name>
SET <Columns> = 'new value'
WHERE <PK> = 'value'</PK></Columns>

Answer 17

Used to update existing data points in an entity

Question 18

DELETE

Answer 18

Is used to delete an instance in an entity. The PK is used to make sure that only that instance is removed

Question 19

Functions in query statements

Answer 19

Functions that can be used are for example COUNT, AVG, SUM, MIN, MAX, etc. These can be used to avoid abundance of data, through calculating a value for a new column using the value of another column

Question 20

REGEXP

Answer 20

Can be used to match to a particular string in instances. Combined with:
? Match zero or one instance of the string
^ Match starting at beginning of string
$ Match starting at end of string
[abc] Match any of the enclosed characters

Question 21

Assign FK to table

Answer 21

CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <primary key table(column primary key)></column></name>

Question 22

Create a view

Answer 22

CREATE VIEW <Name> (<columns>) AS SELECT <columns> FROM <original></original></columns></columns></Name>

Question 23

Joining two tables

Answer 23

SELECT * FROM <table1, table2> WHERE <column_table1> = <column_table2></column_table2></column_table1>

or

SELECT * FROM <table1> JOIN <table2> ON <column_table1> = <column_table2></column_table2></column_table1></table2></table1>

Question 24

GROUP BY

Answer 24

The group by clause allows for grouping of rows that have the same value for a specified column / columns. Can be accompanied by HAVING to select for certain groups to comply to a condition

Question 25

Why use subqueries?

Answer 25

Once you run a query, the values are not saved. If you need to use a query’s value in another query, you need to use a subquery

Question 26

IN used in query after WHERE

Answer 26

Selects only columns that have the string applied after IN in it’s instance

Question 27

SELECT DISTINCT

Answer 27

emits duplicates from SELECT clause

Question 28

m:m relationship creation

Answer 28

An associate entity is created which has two 1:m relationship (it is the many side). It has two FK from the original tables. If it’s PK is not unique enough, it has to be strenghted by one of the original table’s PKs.
If PK is strengthed using a strenghted PK from original table, those two or more PKs are included in the new table.

Question 29

Find all type of questions solution

Answer 29

Double NOT EXISTS

Question 30

UNION

Answer 30

is used to combine the results from two separate queries. Not both queries have to be true for all instances (OR equivalent). Duplicates are excluded

Question 31

INTERSECT

Answer 31

Combines the results from two queries where both have to be true (AND equivalent). Duplicates are excluded.

Question 32

How to create a 1:1 recursive relationshiop

Answer 32

create a table as usual, define PK, define FK as follows:
CONSTRAINT <name> FOREIGN KEY (<foreign key(s)> in same table) REFERENCES <table name>(<PK>)</PK></name>

Question 33

Well-formed

Answer 33

Construction rules are obeyed and no ambiguity present:
- everything is named
- names are meaningful to client
- name entity == PK
- name relationship == FK

Question 34

High fidelity image

Answer 34

Faithfully describes the world it is supposed to represent
Relationships are of correct degree
Data model is complete and understandable
Data model makes sense

Question 35

Cardinality and degree

Answer 35

Cardinality is the number of rows and degree is the number of columns. Only cardinality should be able to change, since new instances can be added to entities

Question 36

Entity types

Answer 36

Independent
Dependent
Associate: created as third entity in m:m relationship
Aggregate: created as supertype or generalization when there are multiple instances that have similar attributes
Subordinate: subtype of another instance (often aggregate)

Question 37

Aggregation of entities

Answer 37

Shared aggregation: One entity owns another but can be owned by other entities as well (denoted by open diamond next to entity)
Composition aggregation: One entity exclusively owns the other entity (denoted by closed diamond next to entity)

Question 38

Integrity rules

Answer 38

entity integrity rule: no component of the primary key can be null
referential integrity rule: foreign key cannot have a value that is not present in the PK of the relationship

Question 39

types of SQL JOINS

Answer 39

INNER JOIN : default, overlapping values
FULL OUTER INCLUSIVE JOIN: all values including overlapping values
FULL OUTER EXCLUSIVE JOIN: all values excluding overlapping values
RIGHT/LEFT EXCLUSIVE JOIN: values from right/left table excluding overlapping values with other table
RIGHT/LEFT INCLUSIVE JOIN: values from right/left table including overlapping values with other table

Question 40

What is a domain?

Answer 40

A domain is a set of values within a database with the same data type

Question 41

Difference primary-, candidate- and alternate key

Answer 41

Primary key is the unique identifier used in an entity
Candidate key is an attribute that could serve as the primary key
Alternate key is the candidate key not chosen as the primary key in an entity

Question 42

Why and how do you use SQL routines?

Answer 42

They are used to add flexibility, improve the productivity of the programmer and facilitatie enforcement of business rules. Once the function is invoked, it returns a value.

CREATE FUNCTION <function_name>(<name> REAL)
RETURNS REAL
RETURN <calculation to be made using name return value;</name></function_name>

To evoke the function: SELECT <function_name>(value)</function_name>

Question 43

CAST and MELT functions

Answer 43

CAST converts a long format data into some aggregated form of data, MELT converts aggregated data into a long format