U8 Databases Flashcards
what is a File-based system?
Data stored in discrete files, stored on computer, and can be accessed, altered or removed by user
Disadvantages of file based system:
- No enforced control on structure of files
- Data repeated in different files
- Sorting must be done manually
- Data may be in different format
- Can’t be multi user - can become chaotic
- Security not sophisticated; user accesses everything
What is a database?
Collection of non-redundant, inter-related data
What is a DBMS?
Software programs that allow databases to be constructed, defined and manipulated
Features of DBMS:
Data management, Data dictionary, Data modelling, Data integrity, Data security
What is data management?
Data stored in relational databases - tables in secondary storage
What does data dictionary do and have in it?
Stores metadata about the database. Has field names, table name, primary/secondary keys etc.
What is data modelling?
Analysis of data objects used in database and identifying relationships between them
What is a logical schema?
Overview of database structure, models the problem and independent of any particular DBMS
What is data integrity in DBMS?
Entire block copied to user’s area when being changed, saved back when done
What is data security in DBMS?
Handles password allocation and verification, backups database automatically. controls user’s view by access rights of individuals or groups of users.
What are the tools in a DBMS?
Developer interface and Query processor
What is the developer interface?
Allows creating and manipulating database in SQL, rather than graphically
What is the query processor?
Handles high-level enquiries. Parses, validates, optimises, and compiles/interprets a query which results in a query plan
What is an entity?
Object/event which can be distinctly identified
What is a table?
Contains group of related entities in rows and columns called an entity set
What is a tuple?
Row or record in a relational database
What is an attribute?
Field or column in a relational database
What is a primary key?
Attribute or combination of them that relates 2 different tables
What is a candidate key?
Attribute that can potentially be a primary key
What is a foreign key?
Attribute or combination of them that can relate 2 different tables
What is referential integrity?
Prevents users or applications from entering inconsistent data
What is the secondary key?
Candidate keys not chosen as a primary key
What is indexing?
Creating secondary keys on an attribute to provide full access when searching on that attribute
Creating a database
CREATE DATABASE <database-name></database-name>
Creating a table
CREATE TABLE <table-name> (…)</table-name>
Changing a table
ALTER TABLE <table-name></table-name>
Adding a primary key
PRIMARY KEY (field)
ADD <field-name>:<data-type></data-type></field-name>
Adding a foreign key
FOREIGN KEY (field) REFERENCES <table>(field)
Adding data to a table
INSERT INTO <table-name>(field1, field2, field3)
VALUES (value1, value2, value3)</table-name>
Deleting a record
DELETE FROM <table-name>
WHERE <condition></condition></table-name>
Updating field in a record
UPDATE <table-name>
SET <field-name> = <value>
WHERE <condition></condition></value></field-name></table-name>
