Database | 9618 CS p1

What are the limitations of using a file-based approach to store data?

- data redundancy ( data is repeated in multiple files)
- data dependency (changes to data means changes to program accessing the data)
- lack of data integrity (entries that should be the same could possibly be different in different places)
- lack of data privacy (all users have access to all data of a single flat file)

What is meant by file-based approach of data storage?

The data is stored in one or more seperate computer files

What is meant by a relational database?

A way of structuring information in tables made of rows and columns

Describe the features of relational database that address the limitations of file based approach

- multiple tables are linked together (reduces data redundancy)
- program data independence means no data dependance
- different users can be given different access rights (which improves data privacy)

What is an entity in a database?

The concept/object in the system that we want to model and store information about

What are attributes in a database?

A data item represented as a field within a table

What is a primary key?

an attribute chosen to ensure that the records in a table are unique

(Unique identifier for a row (tuple))

What is a foreign key?

a column in one table that points to the primary key in another table

Write a DDL statement to create a database

CREATE DATABASE Name;

Write a DDL statement to create a table

CREATE TABLE Name (
Attribute1 datatype,
Attribute2 datatype,
PRIMARY KEY(KeyName) NOT NULL
);

Write a DDL statement to add a new field to a table

ALTER TABLE Name
ADD FieldName DataType;

Write a DML Statement to add data into a table

INSERT INTO TableName
VALUES (Column1data, column2data)

Write a DML statement to update the data/record in a table

UPDATE TableName
SET Column1=Value1, Column2=Value2
Where condition;

Write a DML statement to display/return a value

SELECT Column1, Column2
FROM TableName
Where condition;

What is meant by referential integrity?

- making sure tables do not try to reference data which does not exist
- a primary key cannot be deleted unless all dependant records are already deleted
- a primary key cannot be updated unless all dependant records are already updated
- the foreign keys must be the same datatype as the corresponding primary key

Explain how a retaional database can help to reduce data redundancy

- each record of data is stored once and is referenced by a primary key
- data is stored in individual tables
- tables are linked by relationships
- by enforcing referntial integrity
- by going through the normalization process
- by the proper use of primary and foreign keys

How are primary and foreign keys used to link data?

If Name is the primary key in Table 1, it links to Name which is the foreign key in Table 2

How is DBMS software used to ensure security of data?

- access rights: only certain usernames can read certain parts of the data (can be read only or full access)
- issues usernames and passwords: stops unauthorized access to the data
- encryption of data: If there is unauthorized access to the data, it cannot be understood
- regular backups: a copy of data is available incase it is lost or damaged

What is normalization?

A method to remove or reduce the repetition/redundancy of data

What are the requirements for 1NF?

- no repeated groups of attributes
- all attributes should be atomic (cannot be decomposed into meaningful components)
- no duplicate rows

What are the requirements for 2NF?

- should be in 1NF
- no partial dependency

What are the requirements for 3NF?

- should be in 1NF and 2NF
- no non-key dependency
- no transitive dependency

Name and describe levels of schema of database.

- external schema:
the individual's view of database

- conceptual schema:
describes the views which user of the database might have

- logical schema:
describes how the relationships will be implemented in the logic structure of the database

- physical/internal schema:
describes how the data will be stored on the physical media

Write a statement to delete existing records in a table

DELETE FROM tableName
WHERE condition;