Posted: December 28th, 2014

Understand databases and data management systems

DATA BASE

Introduction

LO1 Understand databases and data management systems

1.1 Analyze the key issues and application of databases within organizational environments…
1.2 Critically evaluate the features and advantages of database management systems………

LO2 Understand database design techniques

2.1 Analyze a database developmental methodology………………………………………..
2.2 Discuss entity-relationship modelling and normalization……………………………….

LO3 be able to design, create and document databases

3.1 Apply the database developmental cycle to a given data set……………………………
3.2 Design a fully functional database (containing at least four inter-relational tables) including user interface………………………………………………………………………
3.3 Evaluate the effectiveness of the database solution and suggest methods of improvement………………………………………………………………………………….
3.4 Provide supporting user and technical documentation………………………………..….

Conclusion……………………………………………………………………………………
References……………………………………………………………………………………

P1.1 Analyse the key issues such as Integrity, Security, Recovery, Concurrency & Atomicity etc. and application of databases within organizational environments such as

Banking, Retail, HR, Education etc.

In recent years, database systems have added support for a number of non-traditional data types such as text documents, images, and maps and other spatial

data. The goal is to make databases universal servers, which can store all types of data. Rather than add support for all such data types into the core database,

vendors offer add-on packages that integrate with the database to provide such functionality.
The typical file-oriented system is supported by a conventional operating system. Permanent records are stored in various files and a number of different application

programs are written to extract records from and add records to the appropriate files.

1.1.1    Key issues of Database:
1.1.1.1 Centralized control of data – Data should be store in a central database server. Any components of database such as table, forms, reports, query access are

based on the user privilege granted from the server.
1.1.1.2    Data Redundancy means the appearance of the same data factor in more than
one field or table of data, or including a data factor as a separate entity when it can be easily inferred from information in existing data fields. This repetition

of the data can cause some distortions and anomalies in the results. The term is also used to refer to the backing up or copying data on additional servers or by other

electronic means as a security measure.

1.1.1.3     Integrity implies the accuracy and consistency of stored data, indicated by an
Absence of any alteration in data between two updates of a data record. Data integrity is imposed within a database at its design stage through the use of standard

rules and procedures, and is maintained through the use of error checking and validation routines.

Course

Course ID    Course name    Course level
C001    HNCCSD    L4
C002    HNDCSD    L5
C003    BSc Computing    L6

1.1.1.4    Security refers to the collective measures used to protect and secure a database or
database management software from illegitimate use and malicious threats and attacks. It is a broad term that includes a multitude of processes, tools and

methodologies that ensure security within a database environment.

1.1.1.5    Backup Duplicate (redundant) copy of data saved during a backup session, and
Available as  replacement of damaged or lost primary data.

1.1.1.6 Recovery means restoration in full or in part of the data stored in deleted or
Damaged data files. In case of file deletion, if the space originally occupied by the deleted file is not overwritten (or the disk is not reformatted after deletion)

the file may be recoverable through common ‘undelete’ programs. In case of damaged files (or files stored on damaged media), custom-written software and sophisticated

equipment is required for any extent of recovery.

1.1.1.7    Atomicity is a feature of databases systems dictating where a transaction must be all-
Or-nothing. That is, the transaction must either fully happen, or not happen at all. It must not complete partially.
1.1.1.8    Consistency, in the context of databases, states that data cannot be written that would
Violate the database’s own rules for valid data. If a certain transaction occurs that attempts to introduce inconsistent data, the entire transaction is rolled back

and an error returned to the user.
1.1.1.9    Isolation, in the context of databases, specifies when and how the changes
Implemented in an operation become visible to other parallel operations. Transaction isolation is an important part of any transactional system. It deals with

consistency and completeness of data retrieved by queries un-affecting a user data by other user actions. A database acquires locks on data to maintain a high level of

isolation.
1.1.1.10 Durability in databases is the property that ensures transactions are saved permanently and do not accidentally disappear or get erased, even during a

database crash. This is usually achieved by saving all transactions to a non-volatile storage medium.
1.1.1.11 Data Independence is the idea that generated and stored data should be kept separate from applications that use the data for computing and presentation. In

many systems, data independence is an innate function related to the multiple components of the system; however, it is possible to keep data contained within a use

application.
1.1.2 Application of databases within organizational environments
Database systems are widely used in different areas because of their numerous advantages. Some of the most common database applications are listed here.
•    Airlines and railways: Airlines and railways use online databases for reservation, and for displaying the schedule information.
•    Banking: Banks use databases for customer inquiry, accounts, loans, and other transactions.
•    Education: Schools and colleges use databases for course registration, result, and other information.
•    Telecommunications: Telecommunication departments use databases to store information about the communication network, telephone numbers, record of calls, for

generating monthly bills, etc.
•    Credit card transactions: Databases are used for keeping track of purchases on credit cards in order to generate monthly statements.
•    E-commerce: Integration of heterogeneous information sources (for example, catalogues) for business activity such as online shopping, booking of holiday

package, consulting a doctor, etc.
•    Health care information systems and electronic patient record: Databases are used for maintaining the patient health care details.
•    Digital libraries and digital publishing: Databases are used for management and delivery of large bodies of textual and multimedia data.
•    Finance: Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading.
•    Sales: Databases are used to store product, customer and transaction details.
Education Advantages
•    Strengthen distance learning programs with apps that make data, class information and feedback accessible in real-time.
•    Improve field research results by giving professors and student’s access to online data management solutions.
•    Reallocate funds typically spent on expensive IT operations and software maintenance.
•    Tracking test scores with fewer mistakes and share student progress.
•    Encouraging parent participation by communicating and gathering feedback.
•    Sharing knowledge, tips, and documents between faculty members.
•    Making it easy to access emergency contact information.
•    Reporting absences and streamlining other administrative tasks.
Banking
•    the highest level of security
•    back up- every hour, after every transactions or the whole backup after midnight
The efficiency of an automated payments process is reliant on the input of accurate data. Data validation is the only way to ensure that the information captured is

accurate.
Data communications costs are usually less with distributed databases because processing can be done locally. For example, with customer records housed locally, a

branch does not incur telecommunications costs to communicate with a headquarters computer that houses the branch’s data.
If there is a hardware and software problem at a computer center that stores and processes all branch information, all the branches may be affected. With distributed

databases, many branches can continue computer processing without waiting for the central location to become operable again.
1.2 Critically evaluate the features and advantages of database management systems

A database is a software program, used to store, delete, update and retrieve data. A database can be limited to a single desktop computer or can be stored in large

server machines, like the IBM Mainframe.
1.2.1 Features of Database

1.2.1.1 Primary Key – The unique data field in a table is assigned a primary key. The primary key helps in the identification of data. It also checks for duplicates

within the same table, thereby reducing data redundancy. There are tables which have a secondary key in addition to the primary key.
1.2.1.2 Foreign Key – The secondary key is also called ‘foreign key’. The secondary key refers to the primary key of another table, thus establishing a relationship

between the two tables.
1.2.1.3 Remote Control Refers to a program’s or device’s ability to control a computer system from a remote location. Remote-control programs for PCs enable you to

access data stored on your home system even when you are travelling.
1.2.1.4 Control provides access to data stored in databases using any one of three types of Record set objects. The Data control enables you to move from record to

record and to display and manipulate data from the records in bound controls.
1.2.1.5 Table Refers to data arranged in rows and columns. A spreadsheet, for example, is a table. In relational database management systems, all information is stored

in the form of tables.
1.2.1.6 Forms A formatted document containing blank fields that users can fill in with data. With paper forms, it is usually necessary for someone to transfer the data

from the paper to a computer database, where the results can then be statistically analysed.
1.2.1.7 Query A request for information from a database. There are three general methods for posing queries:
•    Choosing parameters from a menu: In this method, the database system presents a list of parameters from which you can choose. This is perhaps the easiest way

to pose a query because the menus guide you, but it is also the least flexible.
•    Query by example (QBE): In this method, the system presents a blank record and lets you specify the fields and values that define the query.
•    Query language: Many database systems require you to make requests for information in the form of a stylized query that must be written in a special query

language. This is the most complex method because it forces you to learn a specialized language, but it is also the most powerful.
1.2.1.8 Reports represent a formatted and organized presentation of data. Most database management systems include a report writer that enables you to design and

generate reports.
1.2.1.9 Triggers – In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event (INSERT, DELETE or UPDATE) occurs.
1.2.2 Advantages of database management systems
1.2.2.1 Sharing of data In DBMS, data can be shared by authorized users of the organization. The DBA manages the data and gives rights to users to access the data.

Many users can be authorized to access the same set of information simultaneously. The remote users can also share same data. Similarly, the data of same database can

be shared between different application programs.
1.2.2.2 Reduction of Redundancy/duplication In DBMS, the data redundancy can be controlled or reduced but is not removed completely. Sometimes, it is necessary to

create duplicate copies of the same data items in order to relate tables with each other. By controlling the data redundancy, you can save storage space. Similarly, it

is useful for retrieving data from database using queries.
1.2.2.3 Referential integrity Referential integrity is a relational database concept, which 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. Thus, any primary key field changes must be applied to all foreign keys,

or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.
P2.1- Analyse a database developmental methodology such as SSADM and its phases feasibility study, requirement analysis, requirement specification, physical design

etc.

2.1.1 SSADM – Structured Systems Analysis and Design Methods

•    Feasibility Study – the business area is analyzed to determine whether a system can cost effectively support the business requirements.
•    Requirements Analysis – the requirements of the system to be developed are identified and the current business environment is modeled in terms of the processes

carried out and the data structures involved.
•    Requirements Specification – detailed functional and non-functional requirements are identified and new techniques are introduced to define the required

processing and data structures.
•    Logical System Specification – technical systems options are produced as well as the logical design of update and enquiry processing and system dialogues.
•    Physical Design -a physical database design and a set of program specifications are created using the logical system specification and technical system

specification.

P2.2 – Discuss entity-relationship modelling such as one-to-one, one-to-many and many-to-many relationship and normalization with 1st, 2nd and 3rd normalization to

design database solution for Computer Choice.

2.2.1 Entity-relationship modelling
2.2.1.1 Entity
An entity-relationship model is a systematic way of describing and defining a business process. The process is modelled as components (entities) that are linked with

each other by relationships that express the dependencies and requirements between them, such as: one building may be divided into zero or more apartments, but one

apartment can only be located in one building. Entities may have various properties (attributes) that characterize them. Diagrams created to represent these entities,

attributes, and relationships graphically are called entity–relationship diagrams.
2.2.1.2 Attributes- These are the ‘characteristics’ shared by all entities of a given entity type. There are data elements associated with an entity.
2.2.2.3 Relationship
Entity Relationship: Relationships allow you to describe the connections between different database tables in powerful ways. Once you’ve described the relationships

between your tables, you can later leverage that information to perform powerful cross-table queries, known as joins.
There are three different types of database relationships, each named according to the number of table rows that may be involved in the relationship. Each of these

three relationship types exists between two tables.
•    One-to-one relationships occur when each entry in the first table has one, and only one, counterpart in the second table. One-to-one relationships are rarely

used because it is often more efficient to simply put all of the information in a single table.
•    One-to-many relationships are the most common type of database relationship. They occur when each record in the first table corresponds to one or more records

in the second table but each record in the second table corresponds to only one record in the first table. For example, the relationship between a Teachers table and a

Students table in an elementary school database would likely be a one-to-many relationship, because each student has only one teacher, but each teacher may have

multiple students.

•    Many-to-many relationships occur when each record in the first table corresponds to one or more records in the second table and each record in the second table

corresponds to one or more records in the first table. For example, the relationship between a Teachers and a Courses table would likely be many-to-many because each

teacher may instruct more than one course and each course may have more than one instructor.

2.2.2 Normalisation is primarily a tool used to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of

data.
Database normalization has the rules to be followed while creating databases. Each rule is called as normal form.

First Normal form states – Remove duplicate column and identify each set of related data with a primary key.

Second normal form – Create relationship between master and master detail tables using foreign key.

Third normal form – Remove the fields that do not depend on the primary key.

P 3.1 – Apply the database developmental cycle i.e. learners need to show the workings of different phases of database development methodology to develop database

solution for the Computer Choice given data set.

SSADM using to develop our DB solution

I.    Feasibility Study
a.    Economic feasibility –
•    Budget – no academic course work,
•    Time – 15 weeks
b.    Technical feasibility
•    Hardware –  RAM-1/2GB, Processor – Celeron/Pentium Dual Core, AMD, HDD – 40GB,
•    Software – Windows-7/8, MS Access/Open Office Base, Smart Draw, MS Excel/Open Office Calc, MS Word/ O F Writer.
•    Operational feasibility – Sales, Orders, Inventory.

II.    Requirements Analysis

Mr. Craig is a recently graduate from university and is planning to start up a computer hardware store to sell computers and computer accessories. For Computer Choice

it is important to sell the products at the right price and to keep the details of the customers. Also the company plans to recruit 6-9 members of staff and for every

sale they will earn commission and this should also be included in the database. Manufacturer and supplier details are also important so Mr. Craig wants to include

them in the database as well.

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂