Chapter 4 Flashcards
(15 cards)
What is a database and what is the goal?
A database is a set of interrelated, centrally controlled data files with as little data redundancy as possible.
Where developed to address the proliferation of redundant data in the files of system using the file approach
The hierarchy of a database:
- a file is a related group of records
- a record is a related group of fields
- a field is a specific attribute of interest for the entity
What is database management systems (DBMS)?
software program that manages and controls the data and the interfaces between data and the application programs that use the data stored in the database
Data warehouse
is one or more very large databases containing detailed and summarised data for a number of years and sources used for analytical processing (data analytics) rather than transaction processing
Data mining
using sophisticated analysis, including IA, to discover hypothesised relationships in the data
Logical view of data
Users of the data that are at an external level of the database have a logical view - is how people conceptually organise, view and understand the relationships among data items - MAY NOT modify the underlying data
Physical view of data
Used at the internal view of database and is how the data is actually physically stored in the system - analyses MAY modify the underlying data
What is schema and its three levels?
Schema is a description of the data elements in a database, the relationships among them and the logical model used to organise and describe the data. The levels:
- external - a user´s logical view of their portion of a database (subschema)
- conceptual level - the organisations wide view of the entire database
- internal - a low-level view of the database, describes how data are stored and accessed
What is access rights?
Are permissions granted to create, read, update and delete data (CRUD) connected to those portions of the database the user needs to perform their duties
DBMS languagues
- data defintion language (DDL) - builds the data dictionary, creates the database, describes logical view (SQL ex: CREATE)
- data manipulation language (DML) - changes database content, including data element creations, updates and deletions (SQL ex: UPDATE)
- Data query language (DQL) - contains powerful, easy to use commands to enable users to retrive, sort, order data (SQL ex: SELECT)
What is relational database?
A type of DBMS, is a collection fo two-dimensional tables with each table representing an object about which we wish to collect and store information. Represents the conceptual and external schema as if that data view were truely stored in on table.
Relational database - attributes
- primary key - is the database attribute that uniquely identifies a specific row in a table
- foreign key - is an attribute in one table that is also a primary key in another table and is used to link the two tables
Why have relational data base and not data stored in one large table (which gives the following problems)?
- update anomaly - a nonprimary key item is stored multiple times; updating the item in one location and not the others causes data inconsistencies
- Inset anomaly - results in the inability to add to a database
- delete anomaly - results in the loss of alla information about an entity when a row is deleted
Relational database - basic requirements
- every column in a row must be single valued
- primary keys cannot be null - entity integrity rule!
- Foreign keys, if not null, must have a value that corresponds to the value of a primary key in another table - referential integrity!
- all nonkey attributes in a table must describe a characteristic of the object identified by the primary key
Two approaches to database design:
- normalisation - begins by assuming that everything is initially stored in one large table
- semantic data modelling - the designer uses knowledge of business processes and information needs to create a diagram that shows what to include in the database
