Database

Question 1

What is a database?

Answer 1

In simple terms, a database is a structured collection of data that is stored and organized in a computer system.

Question 2

What does ERD stand for?

Answer 2

Entity relationship diagram

Question 3

What is an ERD?

Answer 3

A visual representation that illustrates the relationships between entities (objects or concepts) in a database.

Question 4

What is an entity?

Answer 4

An entity is a noun that defines one single thing

Question 5

What notoation should you use for ERD’s?

Answer 5

Crows foot

Question 6

What is another name for a record?

Answer 6

Tuple

Question 7

What does DBMS stand for?

Answer 7

Database management system

Question 8

What are the 2 main languages SQL is made up of

Answer 8

• Data Definition Language (DDL)
  Data Manipulation Language (DML)

Question 9

What is SQL

Answer 9

Structured Query Language, is a programming language used for managing and manipulating relational databases.

Question 10

Describe Data Definition Language (DDL) and give examples

Answer 10

DDL commands define the structure of the database
* Allows the DBA or users to describe and name entities, attributes and relationships required for the applications that access it and associated integrity and security constraints.
Examples are :
CREATE TABLE, ALTER TABLE, DROP DATABASE, DROP TABLE, RENAME TABLE

Question 11

Describe Data Manipulation Language (DML)

Answer 11

• DML is data manipulation language which is used for selecting, inserting, updating and deleting data in a database.
  Example :
  . SELECT
  . INSERT
  . UPDATE
  . DELETE

Question 12

What is a query ?

Answer 12

A query is code that interacts with the data base

Question 13

Give some advantages of DBMS

Answer 13

• Data can be managed more easier
• Users can be provided with improved access to data
• Provides an integrated view of an enterprise’s operation
• The risk of inconsistency is improved
  Security can be controlled

Question 14

What is a constraint?

Answer 14

constraints are used to specify rules for the data in a table

Question 15

Give an example of some constraints

Answer 15

UNIQUE, NOT NULL

Question 16

What are the 4 types of relationships in a ERD?

Answer 16

. zero to many
. many to many
. 1 to 1
. 1 to many

Question 17

What relationship do we prefe use in a relational database?

Answer 17

1 to many but we can represent others

Question 18

What is a primary key?

Answer 18

A unique key to identify a record?

Question 19

What is a foreign key ?

Answer 19

This is a field/attribute that provides a link between 2 tables. They are always at the many end of the relationship

Question 20

What is a composite key?

Answer 20

It is a key made up of a primary and foreign key.

Question 21

What is normalisation?

Answer 21

Normalisation is designing the best possible database with reduced data redundancy and best efficiency

Question 22

What is data that has not been normalized called?

Answer 22

0NF (unormalised)

Question 23

What must happen for first normal form (1NF)?

Answer 23

1.) It should only have single(atomic) valued attributes/columns.(1 value per field and no repeating data)
2.) Values stored in a column should be of the same domain(same data type)
3.) All the columns in a table should have unique names.
4.) The order in which data is stored, does not matter.

Question 24

What must happen for second normal form (2NF) ?

Answer 24

1. ) Be in 1NF
   2.) Have no partial dependency

Question 25

What must happen for third normal form (3NF) ?

Answer 25

1.) It is in the Second Normal form.
2.) Doesn’t have transitive(non-key) dependency meaning all fields depend on the primary key and nothing but the primary key.

Question 26

Have a look at inner joins

Answer 26

Powerpoint

Question 27

Name some sql functions

Answer 27

ascii,CHR,initcap, position,format, CONCAT,CURRENT_DATE,date_part,date_trunc

Question 28

What does the ascii function do and write some example code.

Answer 28

It returns the ascii decimal value of a character.
E.g select ascii( ‘e’) = 1010

Question 29

What does the CHR function do?

Answer 29

It returns a character when given a ascii decimal value.
E.g. select CHR(65) = A

Question 30

What does the initcap function do and write some example code.

Answer 30

It changes the case of a given string
E.g select initcap(‘hi i am your lecturer’); = Hi I Am Your Lectuerer

Question 31

What does the position function do and write some example code.

Answer 31

It returns the location of a character in a string. NOTE it starts from 1 and not 0.

E.g.
select position (‘B’ in ‘A B C’) = 3

Question 32

What does the format function do and write some example code.

Answer 32

The format function format arguments based on an input format string.

E.g.
select format (‘Hello %s’ , cust_fname) from customer; = see slide notes for answer

Question 33

What does the CONCAT function do and write some example code

Answer 33

The CONCAT function concatenates strings in query outputs
This function can be used with a separator or without

E.g.
SELECT CONCAT(CIST_FNAME, ‘ ‘, CUST_LNAME) FROM CUSTOMER WHERE CUST_ID = 3; = David mamattah
Note the answer is in a column called concat

Question 34

what do the date_part and date_trunc fucntions do?

Answer 34

date_part gets a specific part of date
e.g. select date_part(‘year’, ‘dob’) from users where users id = 6;

date_trunc does a siilar thing

Question 35

What does the age function do?

Answer 35

It just returns how long its been between 2 given timestamps

e.g.
SELECT age(timestamp ‘2015-01-15’, timestamp ‘1972-12-28’);
= 42 years 18 days]

NOTE IF NOT GIVEN A 2ND TIMESTAMP IT TAKES THE 2ND TIMESTAMP AS THE CURRENT DATE

Question 36

What issue do JSON documents solve ?

Answer 36

JSON documents can store many datatypes meaning that the issue of many datatypes in a database can be solved

Question 37

Name some Json doc store databases?

Answer 37

. Mongo DB
. Couch DB
. Document DB

Question 38

Give some info on key value databases.

Answer 38

It work similar to relational database
* The format of a keyvalue database is a set of keys and values
* They are very fast and have only small records
* The language is not at all complicated
○ Put, Get, Delete
○ Only 2 attributes to get back, keys and values
* Example of key value data stores are as follows
○ Amazon
DynamoDB
○ Couchbase
○ Redis

Question 39

Give some info on graph databases

Answer 39

. They’re more visual and all based on graph theory
. They are used to store information about things and the links between them.
exampes include :
○ Neo4j
○ Tigergraph
GraphDB

Question 40

Give some info on wide column databases

Answer 40

.Similar to relational databases
. A row does not have to be the same length as the row above or below.
Examples include:
○ Google Bigtable
○ Apche Hbase
Scylla

Question 41

What does JSON stand for

Answer 41

Java Script object notation
* It is an open format standard. It consists of key/value pairs.
* The user decides what the keys are and what each value is
* This includes the data types of the values

Question 42

State the main usage and differences of JSON

Answer 42

The main usage of JSON is data transfer between servers and web application
* The main differences between JSON type data and traditional data are:
○ We do not know the structure of each record
○ We do not have a known schema (no need for ERDs)
○ We do not know what the data types will be

Question 43

Is joins necessary when using JSON

Answer 43

No, because we don’t have entities.

Question 44

Describe the steps to create a table that makes use of JSON

Answer 44

Create a table with your name for it and fields.
Make sure one of the fields is called data and its type is jSON
e.g. data json

Question 45

How do you insert into JSON rows

Answer 45

You would do a normal insert statement but where you put the values you add {} and then place your key values

E.g. INSERT INTO json_data (data) VALUES (‘{“fname” : “John”, “lname” : “Doe”, “order” :{“Item” : “IPA”, “QTY” : 6}}’);

Question 46

What are the 2 operators to get data from json rows

Answer 46

-» data by key
-> by text

Question 47

What is the difference between -» and ->

Answer 47

returning data by key(-») just gets rid of the quotation marks in the output

e.g. SELECT data -» ‘customer’ AS customer FROM json_data;

The output is
Lily Smith
Jade Davies
Josh Green-Gardner
(4 rows)

Question 48

Name the basic wildcards and what they do

Answer 48

Wildcard operators are used with the like operator.

One is %

e.g.
a% Finds any values that starts with “a”

%a Finds any values that ends with “a”

Question 49

What are the 5 main aggregate functions and what do they do.

Answer 49

count, sum, avg, min, max