Database Flashcards

Question

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

Answer 1

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.

Answer 2

Powerpoint

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

. Mongo DB . Couch DB . Document DB

Answer 14

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

Answer 15

. 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

Answer 16

.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

Answer 17

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

Answer 18

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

Answer 19

No, because we don't have entities.

Answer 20

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

Answer 21

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}}’);

Answer 22

->> data by key -> by text

Answer 23

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)

Answer 24

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"

Answer 25

count, sum, avg, min, max