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