SLR 23 - databases

Question 1

What are databases used for?

Answer 1

• makes processing data a lot more efficient
• reduces storage requirements
• they avoid redundancy (when piece of data is stored in two or more place)
• allows users to only see relevant data

Question 1

Why are databases normalised?

Answer 1

• so that they can be efficient without compromises to the integrity of the data
• it ensures that entities contain no redundant or repeated data
• allows for faster searching and sorting because the tables are smaller
• easier to maintain than unnormalized because less duplicated data helping to reduce the number of update, insertion and deletion anomalies that occur.

Question 2

How is the update command used?

Answer 2

UPDATE <table> SET <attribute> WHERE <attribute> = <value></value></attribute></attribute>

Question 3

defining a table with SQL?

Answer 3

using the CREATE command - can specify table, attributes and data type and well as entity identifiers

Question 4

other need to know SQL:

Answer 4

• Fixed length string CHAR(size) - Variable length string VARCHAR(size) - - Integer INT(size)
• Number with fractional part FLOAT(size, precision)
• Date DATE (A date in the format YYYY-MM-DD)
• Date and time DATETIME
  (A date and time combined in the format YYYY-MM-DD HH:MM:SS)
• Time TIME
• Year YEAR

Question 5

What are databases?

Answer 5

A structure, persistent collection of data

Question 6

What do you need to analyse about the data before inputting it into a database?

Answer 6

what is being -
- input
- processed
- stored
- what the data entities are

Question 7

What are entities?

Answer 7

categories that help organisations structure and record their data

Question 8

What would be some entities for a library?

Answer 8

• books
• librarians
• rentals
• customers

Question 9

What are attributes ?

Answer 9

Each entity within a database will then be broken down further into more detail, these are called attributes

Question 10

What would be some attributes for the entity students?

Answer 10

• date joined school
• F name
• S name
• DOB
• grades

Question 11

How do you lay out entities and attributes?

Answer 11

• Entity 1(Attribute 1, Attribute2, Attribute3)
• we have to underline our identifier

Question 12

What is an entity identifier?

Answer 12

• an attribute given to each entity which is unique within that table.
• e.g., CustomerID​

Question 13

What do you have to remember about the primary key?

Answer 13

it has to be underlined

Question 14

How can databases be related to each other?

Answer 14

common attributes

Question 15

What are the three possible degrees of relationship between databases?

Answer 15

• one-to-one
• many-to-many
• one-to-many.

Question 16

What does a one to one diagram?

Answer 16

a straight line

Question 17

What does a one to many diagram?

Answer 17

a straight line with crows feet at end

Question 18

What does a many to many diagram?

Answer 18

a lines with crows feet on either end

Question 19

What is a primary key?

Answer 19

an attribute that provides a unique identifier for the entity in a database table

Question 20

Why do we need a foreign key?

Answer 20

• when tables are linked by a shared attribute
• the attribute must be a primary key in one table and is called a foreign key in the other

Question 21

What is a foreign key?

Answer 21

is an attribute in a table which is a primary key in another related table

Question 22

What needs to be created in a many to many relationship ?

Answer 22

linked tables

Question 23

What is a table in first form?

Answer 23

contains no repeating attributes or groups of attributes

Question 24

What is a table in second form?

Answer 24

is in first normal form and contains no partial key dependencies

Question 25

What is a table in third form?

Answer 25

is in second normal form and contains no non-key dependencies

Question 26

What is a composite primary key?

Answer 26

when you combine attributes to form it

Question 27

When does partial key dependency occur?

Answer 27

in databases with composite primary keys when a non-key attribute doesn’t depend on the whole of the composite key.

Question 28

What rea non key dependencies ?

Answer 28

there are no fields that are dependent on other fields that are not part of the key.

Question 29

What is SQL ?

Answer 29

the language used with databases

Question 30

What type of language is SQL?

Answer 30

Structured Query Language is a declarative language - describes the result not the method towards it

Question 31

What are the 4 main SQL commands?

Answer 31

SELECT
UPDATE
INSERT
DELETE

Question 32

The select command

Answer 32

is used for retrieving data from a database table

Question 33

example of how to use the SQL:

Answer 33

SELECT <attribute> FROM <table> WHERE <condition> (ORDER BY <ASC/DEL>) is optional</condition></attribute>

Question 34

What is the update command used for ?

Answer 34

modifying the attributes of an existing entity

Question 35

What do update commands usually use to identify which entities to update?

Answer 35

the primary key

Question 36

How is the delete command used?

Answer 36

is used for removing entities from a database

Question 37

code for the delete command ?

Answer 37

DELETE FROM <table> WHERE <condition></condition>

Question 38

How is the insert command used?

Answer 38

to add new records to an existing table

Question 39

How is the insert command used in code?

Answer 39

INSERT INTO (<column1>,<column 2> , ...) VALUES (<value1>, <value2>, ...)</value2></value1></column1>