DBMS & RDBMS

table

A structure that organizes related data in a set of attributes which helps in uniquely identifying a record or row of data in a table

composite primary key

a primary key formed by combining two or more fields

databse normalisation

a process used to orgranise a database into tables and columns.

one-to-many relationship

a record in table a can relate to zero or one or many records in table B. many records in table b can relate to one record in table a

RDBMS

a software based on the relational model that helps to define and create a relational database

DBMS

a software that helps to define and create a database. It manages data storage, provides tools and facilitates for the retrieval and modification of data.

candidate key

an attribute or set of attributes that uniquely identifies a record

DBMS keys

an attribute or set of attributes which helps in uniquely identifying a record of data in a table

Characteristics of conceptual schema

- important entities and the relationships among them - no primary key is specified

Characteristics of logical schema

- includes all entities -all attributes of interest for each entity are specified - foreign keys are specified - normalisation occurs

features of physical schema

- specification of all tables and columns - foreign keys are used to identify relationships between tables -denormalisation may occur based on user requirements

Factors to consider when designing a logical schema

- specify primary keys for all entities - find the relationships between different entities -find all attributes for each entity - resolve many-to-many relationship to multiple one-to-many relationships -Normalise the databse

data dictionary may include

- who owns or is responsible for the data - what data is available -where the data is located -data descriptions -how the data is used -access rights

database data types

-date and time -string -Boolean -currency

issues related to data integrity

-multiple entries of the same data can also be time consuming and difficult -errors are more likely to occur when complex entries are made in several different files

characteristics of normalised database

-prevents the possibility of data redundancy -improves the data integrity of a database -have consistent and well-structured data -reduces data redundancy and improves access to data

Functions of DBMS

1. Controls the definition and creation of a database 2. controls the maintenance of a database 3. mediates between the data handling applications and the operating system. 4. provide multiple user interfaces 5. provide data storage, retrieval and update facilitates. 6. Provide backup and recovery services

How DBMS can be used to promote data security

1. User authentication 2. Setting and enforcing access rights 3. Data validation 4. Data locking

in designing physical schema

1. convert entities into tables 2. convert relationships into foreign keys. 3. convert attributes into columns 4. modify the physical data model based on physical constraints

importance of data modelling

1. data must be modelled with a clear purpose 2. to identify the entities 3. the attributes of the tables should be necessary and sufficient for the purpose 4. the keys for accessing the tables need to be identified 5. identify the relationships among tables 6. identify relationships and normalisation that reduces data duplication

DBMS tools

1. forms for data display, input and editing 2. reports for producing output 3. charts for displaying data 4. data definition language 5. a means to query the data

issues caused by data redundancy

1. waste of storage 2. update anomaly 3. delete anomly 4. insertion anomaly

Levels of schema

Conceptual, logical and physical

records/tuples

a collection of fields in a row of related data

data dictionary

a data structure that stores meta-data

Primary key

a field or group of fields in a table that uniquely identify each row in that table

meta data

data about data

logical schema

describes the data in as much detail as possible regardless of how they will be physically implemented in the database.

ERD

displays the relationships of a set of entity set stored in a database.

one-to-one relationship

each record in table a relates to only one record in table b

first normal form

each table cell should contain a single value and each record needs to be unique

data dictionary provides

field name, data type, data format, field size, description

secodary/alternate key

fields which are candidate keys but are not selected as a primary key

many-to-many relationship

many records in table A can relate to many records in table b and vice versa.

Conceptual scheme

model that identifies the highest level relationships between the different entities

data modelling

process used to define and analyse data requirements needed to support a given information system in an organisation

data redundancy

refers to the condition in a database which permits unnecessary duplication of data

Physical Schema

represents how the model will be built in the databse

relational integrity

states that any foreign key field must agree with the primary key that is referenced by the foreign key

Database schema

structure or plan of the database which defines the tables and relationship that exist in the database

field/column

the basic unit of data in a database which stores a single piece of information of a particular data type

foreign key

the column or columns of a table that points to the primary key of another table acting as a cross reference between tables

join

used to combine rows from two or more tables based on a related column between them