Topic 1.4 Relational Databases Week 4 Flashcards
(41 cards)
What is a database
- efficiently and centrally coordinates information for a related group of files (database)
File - a group of related records
Record - a group of related fields
A field is a specific attribute of interest for the entity
What is the difference between database and file based legacy
File based legacy - had multiple master file databases that had facts
Database system - keeps facts that used to be in master files in a database so they can be accessed whenever. Not stored somewhere else
Advantages of databases
- data is integrated
- data sharing
- minimises data redundancy and inconsistencies
- data is independent of the programs that use the data
- data is easily accessed for reporting and cross functional analysis
Logical view of data has
Are at an external level of the database. Can access it but don’t worry how it’s stored etc
Physical view data base
Internal view of database. Interested how data is stored in the system
Know what people want access to and what they want stored etc
What does the database management system do
Translates users logical view into instructions as to which data is to be retrieved from the data base
What does operating system do
Translate database management systems into machine code to physically retrieved data
What are schemas
A plan or layout. 3 types
Conceptual level - organisation wide view
External level - individuals users view
Internal level - low level view
What are the 3 different database management system languages
Data definition language (DDL)
- builds the data dictionary
- creates the database
- describes the logical views for each user
- specifies record or field security constraints
Data manipulation language (DML)
- changes the content in the database
Creates, updates, insertions, and deletions
Data Query Language (DQL)
- enables users to retrieve, sort, and display specific data from the database
What does relationally database do
- Represents the conceptual and external schema as if that “data view” were truly stored in one table
What are primary keys
The key that identifies the record in a table e.s in a sales order it’s the sales number
An attribute or combination of attributes that can be used to uniquely identify a specific row (record) in a table
What is a foreign key
Is a key that links two tables together. The foreign key in one table is the primary key in another table
An attribute in one table that is a primary key on another table
- used to link two tables
What are non key attributes
Non-key attributes which store important data about the entity
Why have a set of related tables?
Data stored in one large table can be redundant and inefficient causing the following problems:
- update anomaly
- insert anomaly
- delete anomaly
Rules for relational databases
- every column in a row must have a single value
- primary key cannot be null (empty) also known as entity integrity
- if a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table (referential integrity)
- all other attributes in the table must describe characteristics of the object identified by the primary key
Following these rules allows databases to be normalised and solves the update, insert and delete anomalies
What does normalisation do in relational databases
Initially, one table is used for all the data in a database
The table is decomposed into multiple smaller tables related by primary and foreign keys
This is called 3rd normal form (3NF)
Emma Kolb is a system analyst at a manufacturing company located in the Midwest. She has been asked to analyze the company’s accounting information system and to recommend cost-effective improvements. After noting that the several key managers do not have access to the production and sales information, she recommends that they should be uploaded to the company’s internal network and be made available for search. Implementation of her recommendation would benefit the company by contributing to
Increase data sharing
When the human resources manager wants to gather data about salary and start date by employees and by departments, the manager would use which language?
Data query language
The ________ is responsible for the database.
Database administrator
When the human resources manager wants to build a data dictionary, the manager would use which language?
Data definition language
Tom Wilson is chairman of the Rare Car Club, where he maintains a database of registered rare cars and their history. One table will store the model, make, vehicle identification number, and other characteristics of all of the registered rare cars. Each rare car is uniquely identified by a registration number. A second table will contain data that link each rare car to its most recent estimated fair market value by means of their registration numbers. The primary key in the first table is
Registration number
Emma Kolb is a system analyst at a manufacturing company located in the Midwest. She has been asked to analyze the company’s accounting information system and to recommend cost-effective improvements. After noting that the same production files have been saved and stored independently on several databases, she recommends that they be stored only once. Implementation of her recommendation would benefit the company by contributing to
Reduce data redundancy
Merlin Frodo has been doing custom piercings and tattoos for over thirty years. His home and place of business is a garage in the harbor district of Seattle, Washington. He keeps meticulous records of every job he has ever completed. These have been entered into a computerized accounting information system that his accountant refers to as a “data warehouse.” Merlin is considering adding scarification to his service offerings and has asked his accountant to identify past customers who might be likely candidates for this service. Merlin wants his accountant to engage in
Data mining
Mary Lamb is an insurance sales representative. Her company has provided her with a laptop computer that uses wireless connectivity to access the accounting information system from virtually anywhere in the country. She, and other sales reps, have access to view customer and insurance product information. All sales reps also have access that allows them to enter and cancel customer orders. The permissions for Mary define a ________ in the company’s database management system.
Subschema
