CSC 213 Database Management System 2 Credits

 CSC 213 Database Management                     System 2 Credits

                            Compulsory

Objectives:

At the end of the course, students should be able to:

I. Identify and explain different types and modules of database.

I. List the advantages and disadvantages of database models.

III. Explain data storage, retrieval, security, and integrity.

IV. Use database management system to store, updates, insert, retrieve and delete Records.

V. Identify and explain at least three Dbase administrative functions.

Course Content:

An Overview Database Management

Nature of Database Management system (DBMS)

Different types of DBMS

Models of Database: Network, Hierarchical, Relational, Distributed

Advantages and Disadvantages

Concept of Fields, Records, Access Methods

Data Storage and Retrieval

Data Security and Integrity

Database Administration (DBA)

Methods of Data Organization

Data capturing

Query, sorting, Report generation

Wizard and Design view etc

Application in student record, payroll, inventory control etc intensive Practice with

Relational Data base

Overview of Database

A Data base is a collection of related data organized in a way that data can beeasily accessed, managed and updated.-Any piece of information can be a data, for example name of your school. Database is actually a place where related piece of information is stored and various operations can be performed on it.

For example, a university database might contain information about the following:

Entities such as students, faculty, courses, and classrooms:

Relationships between entities such as students’ enrollment in courses, faculty teaching courses, and the use of rooms for courses.

DBMS

A DBMS(Database Management System)is software that allows creation, definition and manipulation of database. DBMS is actually a tool used to perform any kind of operation on data in database. DBMS also provides protection and security to database. It maintains data consistency in case of multiple users. Here are some examples of popular DBMS, My Sql, Oracle, Sybase, Microsoft Access and IBM DB2 etc.

The alternative to using a DBMS is to use ad-hoc approaches that do not carry over from one application to another; for example, to store the data in files and write  application-specific code to manage it.

Components of Database System

The database system can be divided into four components.

Users: Users may be of various types such as DB administrator, System developer and End users

Database application: Database application may be personal  Departmental, Enterprise and internal

DBMS: Software that allow users to define, create and manages database access, Ex: MySql, Oracle etc.

Database: Collection of logical data.

The functions performed by a typical DBMS are the following:

I. Data Definition: The DBMS provides functions to define the structure of the data in the application. These include defining and modifying the record Structure, the type and size of fields and the various constraints/conditions to be satisfied by the data in each field.

2. Data Manipulation: Once the data structure is defined, data needs to be inserted, modified or deleted. The functions which perform these operations. are also part of the DBMS. This function can handle planned and unplanned data manipulation needs. Planned queries are those which form part of the application. Unplanned queries are ad-hoc queries which are performed on a need basis.

3. Data Security & Integrity: The DBMS contains functions which handle the security and integrity of data in the application. These can be easily invoked: by the application and hence the application programmer need not code these functions in his/her programs.

4. Data Recovery & Concurrency: recovery of data after a system failure and concurrent access of records by multiple users are also handled by the DBMS.

5. Data Dictionary Maintenance: Maintaining the Data Dictionary which contains the data definition of the application is als0 one of the functions of a DBMS.

6. Performance: Optimizing the performance of the queries is one of the important functions of a DBMS. Hence the DBMS has a set of programs forming the Query Optimizer which evaluates the different implementations of a query and chooses the best among them. data stored in the DBMS. This, in conjunction with the high-level interfere to the data, facilitates quick development of applications. Such application is also likely to be more robust than applications developed from scratch because many important tasks are handled by the DBMS instead of being implemented by the application.

DISADVANTACES OF A DBMS

Given all these advantages, is there ever a reason not to use a DBMS?

Complexity: A DBMS is a complex piece of software, optimized for certain kinds of workloads (e.g., answering complex queries or handling many concurrent requests), and its performance may not be adequate for certain specialized applications. Examples include applications with tight real-time constraints or applications with just a few well-defined critical operations far which efficient custom code must be written.

Application data requirement: Another reason for not using a DBMS is that an application may need to-manipulate the data in ways not supported by the query language. I such a situation, the abstract view of the data presented by the DBMS do not match the application's needs, and actually gets in the way. As an example, relational databases do not support flexible analysis of text data (although vendors are now extending their products in thisdirection). If specialized performance or data manipulation requirements arecentral to an, application, the application may choose not to use a DBM:especially if the added benefits of a DBMS (e.g., flexible querying, security concurrent access, and crash recovery) are not required.

Costly

Large in size

NATURE OF DATABASE MANAGEMENT SYSTEM (DBMS)

1. Self-describing nature of the database system: A DBMS catalog stores thedescription of the database. The description is called metadata- This allows theDBMS software to work with different databases.

2. Insulation between program and data: It is called program-data Independence. It allows changing the data storage structure and operations withoutchanging the DBMS access program.

3. Data Abstraction: A data model is used to store hide. storage details and present the user with a conceptual view of the database

4. Support the multiple views of the data: Each user may see a different view ofthe database, which describes only thedata of interest to that user.

5. Sharing of data and multiuser transaction processing: It says that it allowsset of concurrent users to retrieve and to update the Database.Concurrency with the DBMS guarantees that each- transaction is .correctly executed or completeaborted.

DIFFERENT TYPES OF DBMS

1. Numeric and Textual databases

2. Multimedia databases

3. Geographic information system databases

4. Data warehouses

5. Real and active databases

Data Types

A data type is a type of data. ... They may also be more specific types, such asdates, text, memo, and Number formats. Some programming languages require i.e. programmer to define the data type of a variable before assigning it a value.

Data type 

Description 

Auto number 

An auto number field creates unique values autumnally when access creates a new record. The auto number field is primarily keys in access  

Text 

A text field can contain values that are text number or a combination of both. A text field can contain a maximum length of 255 characters 

Memo 

A much larger version of the test field allowing storage of up to 2GB of data a new feature of the Microsoft access memo field that it now supports rich text  formation 

Number 

The number field can store numeric value up to 16 bytes of  data 

Date/time 

The date/ time field allows storage of date and time information. The date/time field now also includes the auto calendar feature

Currency 

The currency data type store value in monetary format. This can be used with financial data 8-byete numbers with precision to four decimal place 

Yes/No

Boolean data storage of true/ false value

OLE object 

The ole object field stores image documents graph etc. from menu windows based programs .the maximum data size   is 2GB although this will slow down data base  

Hyperlink 

The hyper link field type is used to store web addresses. This has a maximum size limit of 1GB of data 

Attachment 

The attachments field type to store image spreadsheet files  document chart and others types of supported file to the record in you data base this is a new future that has been introduced with Microsoft accesses 2007

MODELS OF DATABASE: NET00RK. HIERRACHICAL, RELATIONAL

AND DISTRIBUTED

What is database model? A database model is a type of data model that determines the logical structure of a database and fundamentallydetermines inwhich manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model which uses a table-basedformat.

HIERARCHICAL MODEL

In a hierarchical model, data is organized into a tree-like structure, implying a Single parent foreach record. A sort field keeps sibling records in a particularorder. Hierarchical structures were widely used in the early mainframe databasemanagement systems, such as the Information Management System (IMS) byIBM, and now describe the structure of XML documents. This structure allows one one-to-many relationship between two types of data. This structure is very efficient to describe many relationships in the real world; recipes, table of contents, orderingof paragraphs/verses, any nested and sorted information.

Network model

The network model expands upon the hierarchical structure allowing many-to-many relationships in a tree-like structure that allows multiple parents. It was most popular before being replaced byrelational model, and is defined by theCODASYL specification:

The network model organizes data using two fundamental concepts. Called recordsand sets.Records contain fields (which may be organized hierarchically, as in the programming language COBOL). Sets (not to be confused with mathematical set:) define one-to-many relationshipsbetween records: one owner, many members. Arecord may be an owner in any number of sets, and a member in any numbers of sets.

Network model 

The network model expands upon the hierarchical structure, allowing may-to-many relationships in a tree-like structure that allows multiple parents. It was most popular before being replaced by the relational model, and is defined by the CODASYL specification. 

The network model organizes data using two fundamental concepts, called records and sets, Records contain fields (which may be organized hierarchically, as in the programming language (COBOL). Sets (not to be confused with mathematical sets) define one-to-many relationshps between records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of record may be an owner in any number of sets.

Network Model 

Relational model

The relational model was introduced by E.F. Codd in 1970 as a way to make database management systems more independent of any particular application. It iia mathematical model defined in terms of predicate logic and set theory, and systems implementing it have been used by mainframe, midrange anmicrocomputer systems.

The products that are generally referred to as relational databases in fact implement a model that is only an approximation tothe mathematical model defined by Cod'.Three key terms are used extensively in relational database models: relation, attributes, and domains. A relation is a table with columns and rows. The namedcolumns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take.

CONCEPTS

PRIMARY KEY: A primary key, also called a primary keyword, is a key in arelational database that is unique for each record. It is a unique identifier, such as adriver license number, telephone number (including area code), or vehicle. Identification number (VIN), A relational database must always have one and onlyone primary key.

FOREIGN KEY: In the context of relational databases, a foreign key is a field (orCollection offields).in one table that uniquely identifies a row of another table or e same table. In simpler words, the foreign key is defined in a second table, butit refers to the primary key in the first table.

FIELD: Fields are the smallest units of information- you can access.In spreadsheets; fields are called cells.

RECORDS: A collection of fields is called a record.

ACCESS METHODS:

DATA STORAGE AND RETRIEVAL

WHAT IS DATA STORAGE?

Computer data storage, often called storage or memory, is a technologyconsisting of computer components and recording media used to retain digitaldata, it is a core function and fundamental component of computers. The central processing unit (CPU) of a computer is what manipulates data by performingcomputations.

WHAT IS DATA RETRIEVAL?

Data retrieval means obtaining data from a database management system such asODBMS. In this case, it is considered that data is represented in a structured way,and there is no ambiguity in data. In order to retrieve the desired data thepresent a set of criteria by a query.

DATA SECURITY AND INTEGRITY

DATA SECURITY?

Data security refers to protective digital privacy measures that are applied to prevent unauthorized access to computers, databases and websites. Data security also protects data from corruption. Data security is the main priority for organizations of every size and genre.

Data security is also known as information security (IS) or computer security.

Examples ofdata security technologies include software/hardware disk encryption,backups, data masking and data erasure.

A key data security technology measure is scrambling, where digital data,software/hardware, and hard drives are scrambled and rendered unreadable to unauthorized users and hackers.

DATA INTEGRITY?

Data integrity refers to the overall completeness, accuracy and consistency of data. This can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning data is intact and unchanged.

DATABASE ADMINISTRATION (DBA)

Database administration is the function of managing and maintaining database: management systems (DBMS) software. Mainstream DBMS software such asOracle, TBM DB2 and Microsoft SQL Server need ongoing management.

Database administrators (DBAs) use specialized software to store. And organizedata.

The role may include capacity planning,installation,configuration,databasedesign, migration, performance monitoring, security, troubleshooting, as well asbackup and data recovery.

METHODS OF DATA ORGANIZATION

DEFINATION: Data organization, in broad terms, refers to the method of;classifying and organizing data sets to make them more useful. Some IT expertsapply this primarily to physical records, although some types of data organizationcan also be applied to digital records.

METHODS

There are many ways that IT professionals work on the principle of dataorganization. Many of these are classified under the moregeneral heading of ‘’data management." For example, re ordering analyzing the arrangement of data itemsin physical record 1S part of data organization.

One other main component of enterprise data organization is the. Analysis ofrelatively structured and unstructured data. Structured data is comprised of data intables that can be easily integrated into a database and, from there, fed intoanalytics software or other particular applications. Unstructured data is data that israw and unformatted,. The kind of data that you find in a simple text document,where names, dates and other pieces of information are scattered throughoutrandom paragraphs. Experts have loped tech tools and resources to handlerelatively unstructured data and integrate it into a holistic data environment.

Businesses adopt data organization strategies in order to make better use of the data assets that they have in a world where data sets represent some of the most valuable assets held by enterprises across many different industries. Executives an lother professionals may focus on data organization as a component ofcomprehensive strategy to streamline business processes, get better businessintelligence and generally improve a business model.

DATA CAPTURING

In (Computer Science) any process for converting information into a form that can be handled by a computer.

Input of data, not as a direct result of data entry but instead .as a result of performing a different but related activity. Barcode reader equipped supermarket checkout counters, for example, capture inventory related data while recording a sale.

QUERY, SORTING, REPORT GENERATION

QUERY

A query is a request for information from a database. There are three general methods for posing queries:

Choosingparameters from a menu: In this method, the database system presents 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 data 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 tolearn a specialized language, but it is also the most powerful.

SORTING

The fields of a database can be sorted or rearranged in a number of ways:

Ascending order -- Arrangement- data from lowest to highest in sequence.

Descending order -- Arrangement of sorted data from highest to lowest insequence.

REPORT GENERATION

A report generator is a computer program whose purpose is to take data from asource such as a database, XML stream or a spreadsheet, and use it to produce adocument in a format which satisfies a particular human readership.