Basics of databases Flashcards
What is an ANSI/SPARC structure?
A proposed framework for DBMS
- Internal level
- Conceptual level
- External level
Explain the Internal level in the ANSI structure
- Physical storage of data
- Structure of records on disk
Explain the conceptual level in the ANSI structure
Deals with the organisation of the entire database content
(CREATE table vibes)
Explain the external level in the ANSI structure
Provides a view of the database tailored to a user
(CREATE view vibes)
What are the advantages of using a database?
- Data integrity
- Data consistency
- Enforcement of standards
What are the stages of the system development life cycle (SDLC)?
Planning
Analysis
Design
Implementation
Maintenance
What is a conceptual schema?
Users can see what data is stored in the database and the relationship between data
What is a DBMS?
Stores, modifies and retrieves data
What is SQL made up of?
DDL
DML
DCL
What is DDL?
Data definition language (actual STRUCTURE of database)
- CREATE
- ALTER
- DROP
What is DML?
Data manipulation language (manipulating the data- a level stuff)
- SELECT
- INSERT
- DELETE
- UPDATE
What is DCL?
Data control language (access)
- GRANT
- REVOKE
What are the three types of data model?
Hierarchal
Network
Relational
Explain the hierarchal data model
Data organised in a tree structure
- DOM
Explain the network data model
Data organised in a loose network
Explain the relational data model
Data organised into sets
What is a relation?
A table with columns and rows
What is an attribute?
A named column of a relation
What is a domain?
Set of allowable values for one or more attributes
What is a tuple?
A row in a relation
- Attribute name rows don’t count
What is the degree of a relation?
The number of attributes the relation contains
What is the cardinality?
Number of tuples the relation contains
What is a relational database?
A collection of normalised relations with distinct relation names
What is a candidate key?
A single key or group of multiple keys uniquely identifying table rows
What is a primary key?
A candidate key that is selected to identify tuples uniquely in a relation
What is a foreign key?
An attribute, or set of attributes within one relation that matches the candidate key of some relation
How do we maintain data integrity?
- Restrict domain
- Candidate and primary keys identify tuples
- Foreign keys link relations to each other
What is NULL?
Represents a value for an attribute that is currently unknown or is not applicable
What is entity integrity?
In the base relation, no attribute of the primary key can be NULL and must be unique
What is referential integrity?
If a foreign key exists in a relation, either the foreign key value must match a candidate key value or it must be NULL
What are three ways we can deal with maintaining referential integrity?
Restrict
- Stop user from changing the values in candidate keys
Cascade
- Let the changes flow
Nullify
- Make values in foreign key NULL
How do we logically connect two tables together?
Common attribute
