4.10: section 11: databases and software development

Question 1

define an attribute

Answer 1

-a piece of information which determines the properties of a field or tag in a database or a string of characters in a display.

Question 2

define primary key

Answer 2

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

Question 3

define composite primary key

Answer 3

-a primary key made up of more than one attribute

Question 4

define foreign key

Answer 4

• links tables via a shared attribute

- the attribute must be a primary key in one table

Question 5

what is normalisation

5 points

Answer 5

• the process of coming up with the best possible design for a relational database to allow faster searching and sorting
• no data is duplicated
• data is consistent across tables
• flexible structure allowing as many entries as desired
• user is able to make complex queries

Question 6

what is first normal form

Answer 6

• no repeating attributes or groups of attributes

- the database data is atomic (meaning no single column contains more than one value)

Question 7

what is second normal form

Answer 7

• no repeating attributes or groups of attributes

- no partial dependencies

Question 8

what is third normal form

Answer 8

• no repeating attributes or groups of attributes
• no partial dependencies
• no non key dependencies
• all attributes are dependent on the key

Question 9

why are databases normalised

Answer 9

• easier to maintaining and modifying the database
• faster sorting and searching
• accidental deletion of records is prevented

Question 10

how do you write a select sql command

Answer 10

SELECT attribute FROM table WHERE condition ​ORDER BY ASC/DESC

Question 11

how do you write an update sql command

Answer 11

UPDATE table SET attribute = value WHERE = attribute = value

Question 12

how do you write a delete sql command

Answer 12

DELETE FROM table WHERE condition

Question 13

how do you write an insert sql command

Answer 13

INSERT INTO table (column1, column 2 , …) VALUES (vaule1, value2 , …)

Question 14

how do you define a table with sql

Answer 14

CREATE TABLE Artworks 
(Title VARCHAR(225),
 Artist VARCHAR(255), 
Date YEAR, 
PRIMARY KEY (Title, Artist))

Question 15

how do you do an inner join on a sql select statement

Answer 15

SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name= table2.column_name;