DMBS

Flat File Database

EXCEL. A single huge table. This brought in a lot of redundant data at every entry. Is suitable when we have to retrieve information rarely.

Cloud Computing advantage

Elasticity, scalability, High availability, Easy data distribution, Easier manageability, Lower cost

Domain Integrity

down. specifies that all COLUMNS in a relational database must be declared upon a DEFINED domain

domain definition Pattern matching

eg. nn‐nnnn‐nnnnnn‐nn (for a bank account number)

domain definition Datatype

eg. numeric, alphabetic, date, time, Boolean, etc.

domain definition Length

eg. up to 4 digits, up to 10 characters, etc.

T OR F. (d) Data redundancy is when different versions of the same data are present in the database.

f (in 2 different places)

T OR F. (c) Data redundancy is something to do with storing details of something that no longer exists (for example about a staff that has left the organisation).

false

TO OR F. Data duplication is when different versions of the same data are present in the database.

false

Multi valued attribute

holds multiple values in the column for each occurrence. Example: Phone, Email, skills

Centralized Database

is a database that is located, stored, and maintained in a SINGLE location. This location is often a desktop or server or a mainframe computer. Users can access the database through a computer NETWORK.

Cloud Database

is the ability to distribute data across wide geographical areas and among different servers in one physical data centre. It is based on cloud computing technology made possible by virtualization, something RDBMS were not designed for. If the organisation is outgrowing its legacy RDBMS, it may be time to move data to the Cloud.

Conceptual level

level 2. makes decisions and calculations. (sid+fname+salary). the Conceptual is here. Contains relationships between entities

External level

level 3. users view of database (sid+fname)(sid+fname+salary)

Composite attribute

made up of multiple components, each with independent existence. These can be further sub‐divided. Example: Full name, Address

entity

may have one or more candidate keys, with one selected as primary key.

What is the data type for Student Id (123456) ? VARCHAR or NUMBER? Why?

number

A database system is a computer‐based system to...

record and maintain information.

Referential integrity check

referring to another table to check a value being entered or changed.

Data integrity

refers to the CURRENCY (up-to-date), CONCISTENCT, ACCURACY, COMPLETENESS(not missing) of the data

Referential Integrity

states that table relationships must always be CONSISTENT. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. FK+PK MUST BE CONSISTENT

Data integrity

the MAINTENANCE of, and the assurance of the ACCURACY and CONSISTENCY of, data over its entire life-cycle.

Physical data independence

the capacity to make CHANGES to the INTERNAL schema(CREATE VIEW) without having to change the CONCEPTUAL schema(map of concepts and their relationships)

TO OR F. (g) A database is a collection of related data that can be accessed in a variety of ways.

true

TO OR F. (h) The services provided by a DBMS include data storage, data retrieval, data update, database definition, transaction support, concurrency control, recovery, authorisation, data communication support & integrity checking.

true

domain

what values are allowed in a field

degree of a relation

= cardinality. one-to-one (1:1) one-to-many (1:M)

Cloud Computing

Cloud computing is using a network of remote servers hosted on the internet to store, manage and process data. its cheaper, scalable, High availability, Easy data distribution and manageable.

Advantages of DBMS over a File based system

DATA COLLECTED ONCE. DATA IS SHARED. CONSTRAINTS. NO REDUNDANCY. DATA OWNED BY ORGANISATION data is stored and collected once. data is shared between application. no data redundancy. use constraints. data in the database is owned by the entire organisation.Different groups of users can view the same information.

A database system involves four major components

DATA, USERS, HARDWARE, SOFTWARE

Advantages of DBMS

DIICSEP DATA SHARABILITY and non‐redundance of data INDEPENDENCE of data and program (This is a primary advantage of a database. database and user program can be changed independently) INTERGRITY CENTRALISED CONTROL SECUREITY EFFICIENCY PERFORMANCE

Concerns with File‐based systems:

DIN (ddiin) ISOLATION, DUPLICATION, DEPENDENCE, INCOMPATIBLE, NOT SECURE Isolation, Duplication, Data dependence, Incompatible file formats, Fixed queries of application programs and no security.

Hardware

DISKS where data resides, together with a processor, control units, channels etc. The database is assumed to be too large to be held in its entirety in the computer's primary storage; therefore there is a NEED for software to manage that data.

WHAT refers to the situation where different versions of the same data are stored at different places because they weren't updated consistently?

Data redundancy

Network Database

HIERARICAL+M2M. NO CONSTAINTS similar to hierarchical database but also allowed M2M.However, this model did NOT allow for constraints.

Data

IS. A database is a collection of data which is both INTERGRATED (combination of files) and SHARED (share data with different users)

query Optimization

PROCESS A QUERY FAST. Since database structures are complex, we find that data can be collected from a database in different ways - each way requires different processing times. The purpose of query optimization is to find a way to PROCESS a query in MINIMUM TIME. If we find that some queries run slow, we could try to optimize the query. Before trying to optimize a slow query, we need to understand what makes it slow.

The three level schema architecture can be used to explain the concept of

Physical and logical data independence.

entity integrity

ROWS. VERTICALLY. checks that the designated primary key has a unique value in every row.

Characteristics of a Relational table

Relational table = consists of columns, and rows. Each row = a single entity occurrence unique. table columns has an attribute and column has unique name. * Each row and column intersection represents a single data value and must be atomic. * All values in a column must belong to the same data type and same domain. * The sequence of the rows and columns does not matter to the DBMS * Each table must have an attribute or a combination of attributes that uniquely identifies each row.

Integrity checks on data items can be divided into 4 groups:

TYPE(e.g. that its numer and not character) , REDUDNACY(no redudnacy), RANGE (not after 2011), CONSTRAINTS (such as a limit on the number of x‐ray results for each patient)

alternate key

The other candidate keys which are not the primary key

transaction

Transaction= one work, made up of one or many actions. For example, a transaction may be to transfer some amount from account A to account B. Even though it is made of different actions (i.e. viewing account A, taking an amount out, saving current status, viewing account B, adding some amount in, saving current status), the whole set forms a single transaction (i.e. moving from account A to account B)

domain definition Range

eg. a value between 1 and 10

domain definition Discrete values

eg. either 'A' or 'B' or 'C'

Database Schemas: Internal level

level 1. how data is stored. CREATE TABLE.

t

t

T OR F. Data dependence is when the value of an attribute depends on another attribute (for example age depends on data of birth)

yes and no. you can calulate the bith with derived attribute. A dependency occurs when information stored in the same database table uniquely determines other information stored in the same table.

TO OR F. (e) Data duplication is when the same set of data is present in more than one location.

yes in file-based system

DBMS what happens

1. A user issues an access REQUEST 2. The DBMS intercepts the request and INTERPRET it. 3. The DBMS performs the necessary operations on the stored database. REACT

A transaction should always satisfy the ACID properties:

AT-COID A - Atomic (The whole transaction should go through as one unit) C - Consistent (The database should always remain in a consistent state.) I - Isolated( All transactions must run hidden/unavailable to another.Partial effects of a transaction should not be visible to other transactions) D - Durable ( Once a transaction is committed, all changes made by this transaction should be permanently saved in the database. Any failures that may happen later, should not affect the transaction.)

A key

a data item that allows us to UNIQUELY IDENTIFY individual occurrences or an entity type.

regarding security, DBMS should provide ...

Authorization (allowed access) and Authentication( determines whether a user is the person they claim to be)

Data Validation

CHECK, FIRST ENTER DATA+WHEN UPDATED The best time to CHECK the validity of data is when it is FIRST entered and when it is being UPDATED Such checks can be carried in the following ways: 1. The user checks it when entering it. A data entry program might facilitate this by allowing the user to 'review' the data before permanently saving it. 2. The data entry program or form has inbuilt checks to carry out on the data when it is being entered. These checks are effectively 'programmed' into that particular form or program. 3. The database schema defines the data validation checks to be carried out when data is entered/updated. This last method is considered the most desirable since each check only has to be defined once and will always be applied by default. DBMSs that provide this method, incidentally, are said to provide integrity independence.

Disadvantages of DBMS

COMPLEX, SIZE, COST, PERFORMACE, HIGH IMPACT ON FAILURE Complexity * Size * Cost * Additional hardware costs * Cost of conversion * Performance * Higher impact on failure

Relational Database Management System (RDBMS)

CONSTAINTS. M2M. AFFORDABLE. NOT FAST. allowed many‐to‐many mapping and to define constraints. The availability of low‐cost servers made them widely practical and relatively affordable. However, in the web era and full computerization of business, high speed transactions and faster response rates are desired.

Types of Database

Flat File, Hierarchical, Network, Centralized, Cloud - Database and RDBMS and DDBMS.

T OR F. (b) Data dependence is when the file structure is defined in the program code.

In file-based then yes (data dependence=Two fields A,B are dependent of knowledge of one, say A, is needed to understand the field B. An example would be 'Student' and 'Student_Grades'. 'Student_Grades' makes no sense without the existence of 'Students'.

Logical data independence

LCE the capacity to make CHANGES to the CONCEPTUAL schema without having to change the external schema(end user view)

The objective of the database approach

MORE ORGANISED. PROTECT INTERGIRY. to centralise the definition and management of data so that it can be better utilised as an organisational resource. The goals being maximum AVAILABILITY and utilisation of data, while at the same time protecting its integrity.

Hierarchical Database

NOT M2M. is similar to the folder structure on a personal computer. (Home-Photo-Fun). restricted to one‐to‐many structure. The structure is similar to an upside‐down tree. This model did not allow many‐to‐many relationships.

Attribute types

SINGLE VALUES, MULTI VALUED, COMPOSITE, DERIVED.

Some basic ways to optimize the query performance ...

SIRE. SCHEMA, INDEXING, REWRIE, EXTERNAL Change schema and indexing. Rewrite Query. Pick only the required columns. Use external cache. Pick the right engine for you data.

DBMS

SOFTWARE that allows people to USE and modify DATA stored a database, such as MySQL. it provides the methods for accessing the database and manages security, integrity and recovery (if lost) of the data. database management system.

Distributed Database Management System (DDBMS).

STORADGE OVER MULTIPLE DEVICES, ACTS IF STORED ON SAME. A distributed database is a database in which storage devices are not all attached to a common CPU. It may be stored in multiple computers in the same physical location, or may be dispersed over a network of interconnected computers. A DDBMS manages as if it were all stored on the same computer. It synchronises the data periodically, and where multiple users access the same data, it ensures that updates and deletes performed on the data at one location is automatically reflected in the data stored elsewhere.

database schema.

The DESCRIPTION of a database. a 3 level architecture to separate the user applications from the physical database. you have the internal-, Conceptual- and the external- level. ICE

Integrity constraints

a set of RULES TO BE OBEYED when inserting, updating, or deleting a database to preserve an error‐free state. Integrity checks are important checks on data items and record types. Even if redundancy is eliminated, the database may still contain incorrect data.

Users

There are three broad classes of users: DATABASE ADMINISTARTOR, PROGRAMMER, END-USER * the database administrator (DBA), who controls all operations on the database. * the application programmer, responsible for writing programs in some high‐level language such as COBOL, C++, etc. * the end‐user, who accesses the database via a query language.

To improve data integrity the DBMS can provide the following types of services:

VALIDATION, SECURITY, CONTROLS, BACKUP, AUDIT FACILITIES. Data validation: Where data is checked for correctness when entered or changed. Security of data: Where only authorised users and software can access the data. Concurrency controls: Where concurrent access to the data is controlled to prevent inconsistency in the data. Backup and recovery: Where damaged or lost data can be recovered. Audit facilities: Where changes to data can be traced after they have occurred.

File‐based systems

a collection of application programs that performs services for the end‐users (e.g. produce reports). Each program defines and MANAGES its OWN DATA. EARLY attempt to computerise manual filling system but with NO STRUCUTE. works well as long as the number of items to be stored is small. this is storing in EXCEL fex.

Derived attribute

can be calculated from other attributes (from current relation or other relations) or a static data. Example: Age, Total

Single valued attribute

canNOT be sub‐divided into smaller components. Example: Salary, Position, Student Id, Staff No

candidate key

cannot have a null value, no duplicates, unique. If the candidate key is a composite key the combination is unique, and no part of the key should be null.

VARCHAR

character up to

What is the assumption made if we had (Staff + Class + Student Id) together to form the primary key?

compound primary keys. that staff+class+student id, combined would identify the student name, result, grade etc.