8 databases Flashcards
(23 cards)
limitations of a file based approach
- storage space is wasted when data items are duplicated by the separate applications and some data is redundant
- data can be altered by one application and not by another, it becomes inconsistent
- enquiries depend on the structure of the data so data is not independent
benefits of a relational database
- storage space is not wasted as data items are only stored once, meaning no redundant data
- data altered in one application is available in another, so data is consistent
- enquiries are not dependent on the structure of the data, so data is independent
what is a candidate key?
an attribute in a table that could be a primary key
what is a primary key?
a unique identifier for a table, it is a special case of a candidate key
what is a foreign key?
a set of attributes in one table that refer to the primary key in another table
what is a relationship?
its formed when one table in a database has a foreign key that refers to a primary key in another table in the database
what is referential integrity?
the database must not contain any values of a foreign key that are not matched to the corresponding primary key
E-R diagram
visual representation of how the entities in a database are related
normalization
the process of organizing data to be stored in a database into two or more tables and relationships between the tables, so that data redundancy is minimized
what is first normal form, second and third
1NF - entities do not contain repeated groups of attributes
2NF - entities are in 1NF and any non-key attributes depend upon the primary key. there are no partial dependencies
3NF - entities are in 2NF and all non-key attributes are independent. the table contains no non-key dependencies
DBMS
systems software for the definition, creation and manipulation of a database
data management
the organization and maintenance of data in a database to provide the information required
data dictionary
a set of data that contains metadata (data about other data) for a database
- e.g. table name, field name, primary key
data modelling
the analysis and definition of the data structures required in a database and to produce a data model
logical schema
a data model for a specific database that is independent of the DBMS used to build that database
access rights
the permissions given to database users to access, modify or delete data
developer interface
feature of a DBMS that provides developers with the commands required for definition, creation and manipulation of a database
SQL
the structured query language used with relational databases for data definition and data modification
query processor
feature of a DBMS that processes and executes queries written in SQL
- allows user to enter criteria
- searches for data that meets entered criteria
Data definition language (DDL)
a language used to create, modify and remove the data structures that form a database
commands:
-CREATE DATABASE
- ALTER TABLE
- PRIMARY KEY
-FOREIGN KEY…REFERENCES…
Data manipulation language
a language used to add, modify, delete and retrieve the data stored in a relational database
commands:
- select from
- where
- order by
- group by
- inner join
- sum
- count
- avg
- insert into
- delete from
- update
field
column in a table
entity
anything that data can be stored about
