Basics of databases

Question 1

What is an ANSI/SPARC structure?

Answer 1

A proposed framework for DBMS
- Internal level
- Conceptual level
- External level

Question 2

Explain the Internal level in the ANSI structure

Answer 2

• Physical storage of data
• Structure of records on disk

Question 3

Explain the conceptual level in the ANSI structure

Answer 3

Deals with the organisation of the entire database content
(CREATE table vibes)

Question 4

Explain the external level in the ANSI structure

Answer 4

Provides a view of the database tailored to a user
(CREATE view vibes)

Question 5

What are the advantages of using a database?

Answer 5

• Data integrity
• Data consistency
• Enforcement of standards

Question 6

What are the stages of the system development life cycle (SDLC)?

Answer 6

Planning
Analysis
Design
Implementation
Maintenance

Question 7

What is a conceptual schema?

Answer 7

Users can see what data is stored in the database and the relationship between data

Question 8

What is a DBMS?

Answer 8

Stores, modifies and retrieves data

Question 9

What is SQL made up of?

Answer 9

DDL
DML
DCL

Question 10

What is DDL?

Answer 10

Data definition language (actual STRUCTURE of database)
- CREATE
- ALTER
- DROP

Question 11

What is DML?

Answer 11

Data manipulation language (manipulating the data- a level stuff)
- SELECT
- INSERT
- DELETE
- UPDATE

Question 12

What is DCL?

Answer 12

Data control language (access)
- GRANT
- REVOKE

Question 13

What are the three types of data model?

Answer 13

Hierarchal
Network
Relational

Question 14

Explain the hierarchal data model

Answer 14

Data organised in a tree structure
- DOM

Question 15

Explain the network data model

Answer 15

Data organised in a loose network

Question 16

Explain the relational data model

Answer 16

Data organised into sets

Question 17

What is a relation?

Answer 17

A table with columns and rows

Question 18

What is an attribute?

Answer 18

A named column of a relation

Question 19

What is a domain?

Answer 19

Set of allowable values for one or more attributes

Question 20

What is a tuple?

Answer 20

A row in a relation
- Attribute name rows don’t count

Question 21

What is the degree of a relation?

Answer 21

The number of attributes the relation contains

Question 22

What is the cardinality?

Answer 22

Number of tuples the relation contains

Question 23

What is a relational database?

Answer 23

A collection of normalised relations with distinct relation names

Question 24

What is a candidate key?

Answer 24

A single key or group of multiple keys uniquely identifying table rows

Question 25

What is a primary key?

Answer 25

A candidate key that is selected to identify tuples uniquely in a relation

Question 26

What is a foreign key?

Answer 26

An attribute, or set of attributes within one relation that matches the candidate key of some relation

Question 27

How do we maintain data integrity?

Answer 27

- Restrict domain - Candidate and primary keys identify tuples - Foreign keys link relations to each other

Question 28

What is NULL?

Answer 28

Represents a value for an attribute that is currently unknown or is not applicable

Question 29

What is entity integrity?

Answer 29

In the base relation, no attribute of the primary key can be NULL and must be unique

Question 30

What is referential integrity?

Answer 30

If a foreign key exists in a relation, either the foreign key value must match a candidate key value or it must be NULL

Question 31

What are three ways we can deal with maintaining referential integrity?

Answer 31

Restrict - Stop user from changing the values in candidate keys Cascade - Let the changes flow Nullify - Make values in foreign key NULL

Question 32

How do we logically connect two tables together?

Answer 32

Common attribute