Relational databases ( Topic 10 ) Flashcards Preview

AIS > Relational databases ( Topic 10 ) > Flashcards

Flashcards in Relational databases ( Topic 10 ) Deck (45)
Loading flashcards...
1
Q

Data Hierarchy

A

Field
- Attributes about an entity

Record
• Related group of fields

File
- Related group of records

Database
– Related group of files

2
Q

Database hierarchy diagram

A

( image document)

3
Q

Database Management System (DBMS):

A


• Interface between software applications and the data in files.
- The program that manages and controls the data and the interfaces between the data and the application pro- grams that use the data stored in the database.

4
Q

Database Administrator (DBA):


A

• Person responsible for maintaining the database.

- The person responsible for coordinating, controlling, and managing the database.

5
Q

Data Dictionary

A

Information about the structure of the database

- Field names, descriptions and uses.

6
Q

Data Warehouses for Business Intelligence

A

Data Warehouse
- Very large databases containing detailed and summarized data for a number of years that are used for analysis rather than transac- tion processing.
• Contains both detailed and summarised data for a number of years. 

• Used for analysis rather than transaction processing.

Business Intelligence 

- Analyzing large amounts of data for strategic decision making.
• Online analytical processing (OLAP) 

• Data mining. (e.g. market-basket analysis; RFM method; statistical 
analysis) 
- Using sophisticated statistical analysis to “discover” unhypothesized relationships in the data.

Proper Controls 

• To reap significant benefits from data warehousing.

7
Q

File-oriented systems versus database systems diagram

A

( image document )

8
Q

Advantages of Database Systems

A

Data Integration
• Files are logically combined and made accessible to various systems.

Data Sharing
• With data in one place it is more easily accessed by authorised users.

Minimising Data Redundancy and Data Inconsistency
• Eliminates the same data being stored in multiple files, thus reducing inconsistency in multiple versions of the same data.

Data Independence
• Data is separate from the programs that access it. Changes can be made to the data without necessitating a change in the programs and vice versa.

Cross-Functional Analysis
• Relationships between data from various organisational departments can be more easily combined.

9
Q

Database Users and Designers

A

Different users of the database information are at an external level of the database. These users have logical views of the data.
At an internal level of the database is the physical view of the data which is how the data is actually physically stored in the system.

Designers of a database need to understand user’s needs and the conceptual level of the entire database as well as the physical view.

10
Q

Functions of the DBMS: To support multiple logical views of data

A

diagram in image document

11
Q

Record Layout

A

Document that shows the items stored in a file, including the order and length of the data fields and the type of data stored.

12
Q

Logical view

A

How people con- ceptually organize, view, and understand the relationships among data items.

13
Q

Physical view

A

The way data are physically arranged and stored in the computer system.

14
Q

Schema

A

A description of the data elements in a database, the relationships among them, and the logical model used to organize and describe the data.

15
Q

Three levels of schemas

A

conceptual-level schema - The organization-wide view of the entire database that lists all data elements and the relation- ships between them.

external-level schema - An in- dividual user’s view of portions of a database; also called a subschema.

internal-level schema - A low-level view of the entire database describing how the data are actually stored and accessed.

16
Q

subschema

A

A subset of the schema; the way the user defines the data and the data relationships.

17
Q

DBMS Languages

A
Data Definition Language (DDL): 
•	Builds the data dictionary 

•	Creates the database 

•	Describes the subschema 

•	Specifies record or field security constraints. 

Data Manipulation Language (DML): 

• Changes the content in the database
- Updates, insertions, and deletions.

Data Query Language (DQL):
• Enables the retrieval, sorting, and display of data from the database.

18
Q

Relational Database

A

Relational data model represents the conceptual and external level schemas as if data are stored in tables.
Although the conceptual view appears to the user that this information is in one big table, it really is a set of tables that relate to one another.

Table:
Each row, a tuple, contains data about one instance of an entity.
- This is equivalent to a record.


Each column contains data about one attribute of an entity.

- This is equivalent to a field.

19
Q

data model

A

An abstract representation of database contents.

20
Q

Relational data model (definition)

A

A two-dimensional table repre- sentation of data; each row rep- resents a unique entity (record) and each column is a field where record attributes are stored.

21
Q

Tuple

A

A row in a table that contains data about a specific item in a database table

22
Q

Primary key

A

Database attribute, or combination of attributes, that uniquely identifies each row in a table.

23
Q

Foreign Key

A

An attribute in a table that is also a primary key in another table; used to link the two tables.

24
Q

Relational database table ( diagram)

A

notes on images document

25
Q

Why have a set of Related Tables and problems caused by not doing so?

A

Data stored in one large table can be redundant and inefficient causing the following problems:

• Update Anomaly


  • Changes to existing data are not correctly recorded.

  • Due to multiple records with the same data attributes. 


• Insert Anomaly

-Unable to add a record to the database. 


• Delete Anomaly 

-Removing a record also removes unintended data from the database. 


26
Q

Design Requirements for Relational Database

A

Every column must be single valued.

Primary keys must contain data (not null) known as entity integrity.

Foreign keys must contain the same data as the primary key in another table – referential integrity

All non-key attributes must identify a characteristic of the table identified by the primary key.

27
Q

Two approaches for database design

A

Normalization - Following relational database creation rules to design a relational database that is free from delete, insert, and update anomalies.

Semantic data modeling - Using knowledge of business pro- cesses and information needs to create a diagram that shows what to include in a fully nor- malized database (in 3NF).

28
Q

Database Design Process ( diagram)

A

images document

29
Q

Data Modelling

A

Process of defining an information system so it represents an organisations requirements.

30
Q

Occurs at two stages of the design process

A
  • System analysis 


* Conceptual design

31
Q

Data models

A
  • Entity-relationship (E-R) diagrams 


* REA data models 


32
Q

Entity-Relationship Diagrams

A

Entity-Relationship (E-R) diagrams are a graphical way to diagram the relationships between entities

An entity is anything that the organization wants to collect and store information
For AIS database design, the decision is understanding what entities should be included.

REA diagrams are a combination of E-R diagrams used in the development of an AIS database.

33
Q

E-R Diagram Variations (diagram )

A

Look at image document

34
Q

Cardinalities

Describe the nature of relationships between entities.

A

• How many instances of one entity can be linked to each specific instance of another entity. 

• Minimum can be: 0 or 1 

• Maximum can be: 1 or Many 

( diagram on images document)

35
Q

Types of relationships

A
Three Types of Relationships 
Relationship type is based on maximum cardinality 
•	• One-to-One 
•	• One-to-Many 
•	• Many-to-Many 
( diagram on image document )
36
Q

Resources-Events-Agent Diagrams

A

Resources-Events-Agent Diagrams

Developed for designing AIS 
Categorising entities into 
Resources

   o Things that have economic value 
Events
   
o Business activities that Management wants to manage and control 
Agents 
   o People and organisations that participate in events
37
Q

Basic REA Template

A

Diagram in image document

38
Q

REA Diagram Rules

A

Each event is linked to at least one resource that it affects. Each event is linked to at least one other event.

Types of links (relationships)

Get events - EVERY EVENT ENTITY MUST BE LINKED TO AT LEAST ONE OTHER EVENT ENTITY
Give events

Participation events

- Each event is linked to at least two participating agents. - EVERY EVENT ENTITY MUST BE LINKED TO AT LEAST TWO PARTICIPATING AGENTS

39
Q

usiness Cycle Give–Get Relationships

A

On image document

40
Q

Developing an REA Diagram

A

Developing an REA Diagram

Identify events about which management wants to collect information.
Identify resources affected by each event and agents who participate in those events.
What economic resource is reduced by a “Give” event?


What economic resource is acquired by a “Get” event?


What economic resource is affected by a commitment event?

Determine cardinalities of each relationship.

( diagram in image document- for revenue, expenditure cycles and payroll subcycle )

41
Q

Combining REA Diagrams

A

Individual REA diagrams can be combined to form an integrated REA diagram by:
• Merging redundant resource entities (e.g., inventory is acquired in expenditure cycle and reduced in the sales cycle)
• Merging redundant event entities (e.g., disburse cash event appears in the expenditure cycle and the payroll cycle)

Integrated REA on image document

42
Q

Rules for Creating Integrated REA Diagram

A
  • Every event must be linked to at least one resource. 

  • Every event must be linked to two agents who participate in that 
event. 

  • Every event that involves the disposition of a resource must be linked to an event that involves the acquisition of a resource. 

  • Every resource must be linked to at least one event that increments that resource and to at least one event that decrements that resource. 

  • If event A can be linked to more than one other event, but cannot be linked simultaneously to all of those other events, then the REA diagram should show that event A is linked to a minimum of 0 of each of those other events. 

43
Q

Cardinality Effect of Merging Resources

A

diagram in image document

44
Q

REA to Database Steps

A
  • Create a table for each distinct entity in the diagram and for each many-to-many relationship.
  • Assign attributes to appropriate tables.

  • Identify primary keys (Attributes that uniquely identifies each record)
  • For M:N relationships the primary key consists of two attributes that represent the primary keys of each entity linked in that relationship.
  • Identify remaining attributes for table.

  • Use foreign keys to implement one-to-one and one-to-many relationships.
  • An attribute of one entity that is itself the primary key of another entity.

(Example diagram on image document)

45
Q

What an REA Diagram Reveals About an Organization

A
  • BUSINESS MEANING OF CARDINALITIES

- UNIQUENESS OF REA DIAGRAMS