Section
Open allClose all
Instructions: Clicking on the section name will show / hide the section.
Introduction
This course introduces the fundamentals of Database Management Systems. Topics in this course include introduction to database and its development methodology, data models and design, query language, database administration and not to exclude the basic principles behind data warehousing in preparation for data analytics.
This course intended to teach students on the process of developing database from the scratch and builds knowledge on various software engineering techniques, programming skills and file organisation to show the role of databases in contemporary computingContinuous Assessment Schedule
Assessment Cover Page
Students are required to submit all the assessments by using the attached cover page:
Topic 1 - Introduction to Database
- By the end of this topics, you will be able to:
1. To describe traditional file systems, its limitations and the concepts and needs of database
2. To identify evolution of database
3. To discuss the different types Database Architecture
4. To identify roles of people in managing database
"Hi Every one!
Today, as our first lesson, we will discuss about Traditional File Based Systems, its limitation which becomes a reason for the raise of database.
So, what is a database? Well, it's like an organized collection of data that is logically related. But here's the thing: data by itself isn't very useful. It needs to go through some processing, summarizing, and organizing to become valuable for decision makers and knowledge workers like yourself.
In this case, can we say, databases are all about facilitating communication and providing meaningful insights. They enable decision makers and knowledge workers to access and understand data in a way that supports better decision-making and enhances our overall knowledge.
Hope that helps to give you a glimpse of what this capture about and you are required to complete the assigned task to gain more insights.
Thank YouIn your opinion, why Database Management Systems (DBMS) is important in running businesses in this digital era?
Discuss the criteria that you will be using to determine the best database architecture to be applied for your design.
Finally, we are at the nd of this topic. In this topic you have learnt a basic introduction of Database evoloution which started from file based system. Then we also discuss different types of Database Architecture. Finally, we have touched on the existence of different role in managing and maintaining database systems.
That is all about Topic - A brief introduction on Database. See you all in the next topic - Data Modeling & Entity Relationship Diagram. Bye !
Topic 2 - Data Modeling
By the end of this topics, you will be able to:
1. Describe the concepts of data modeling
2. Discuss the notations in data modeling
INTRODUCTIONIn this lecture, we will be exploring about data modeling. Data modelling is the process of creating a simplified diagram of a software system and the data elements it contains, using text and symbols to represent the data and how it flows. Data models provide a blueprint for designing a new database or reengineering a legacy application.
Topic 3 - Relational Modeling
IntroductionBy the end of this topics, you will be able to:
- to learn about relational models and its properties
- to learn about relational data structure , database relations, it keys
- to learn about integrity constraints
Relational Model Introduced by CODD in 1970. It is the fundamental basis for the relational DBMS. The basic structure of relational model is the mathematical concept of a RELATION mapped to the 'concept' of a table (tabular representation of relation). The relational model for database management is an approach to logically represent and manage the data stored in a database. In this model, the data is organized into a collection of two-dimensional inter-related tables, also known as relations.
Relational Model Terminology
Relational Properties
- There are no duplicate tuples.
- Tuples are unordered.
- Attributes are unordered.
- All attribute values are atomic.
- No two tables can have the same name in a database.
- Attributes (columns) cannot have the same name in a table.
- All values within an attribute are from the same domain.
Key in a Relational Database is a column (attribute) or group of columns (attributes) used to uniquely identify records in a table of a relational database such as those created and managed by Microsoft SQL Server. The picture below shows all the types of keys used in database with its description.
Integrity Constraints
Integrity Constraints are the protocols that a table's data columns must follow. These are used to restrict the types of information that can be entered into a table. This means that the data in the database is accurate and reliable. You may apply integrity Constraints at the column or table level.
There are two types of integrity:
a) Entity Integrity and
b) Referential Integrity
Referential Integrity
Summary
In this topic, you have learned about relational models and its properties, relational data structure , database relations, it keys and integrity constraints.Topic 4 - Normalization
Learning Outcome
IntroductionBy the end of this topics, you will be able to:
1. to learn about normalization and its purposes in logical design
2. to learn the process of normalization
From an information management point of view, possibly the most vexing and destructive problems are created through uncontrolled data redundancies. Such redundancies produce update and delete anomalies that create data integrity problems. The loss of data integrity can destroy the usefulness of the data within the database. (If necessary, review Chapter 1, Section 1-6b, “Data Redundancy”, to make sure that your students understand the terminology and that they appreciate the dangers of data redundancy.)
Table structures are poor whenever they promote uncontrolled data redundancy. For example, the table structure shown in Table 4.1 is poor because it stores redundant data. In this example, the AC_MODEL, AC_RENT_CHG, and AC_SEATS attributes are redundant. (For example, note that the hourly rental charge of $58.50 is stored four times, once for each of the four Cessna C-172 Skyhawk aircraft – check records 1, 2, 4, and 9.)
Table 4.1Aircraft
Normalization
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency
Process of Normalization
Beginning with either a user view or a data store developed for a data dictionary (see Chapter 8), the analyst normalizes a data structure in three steps, as shown in the figure below. Each step involves an important procedure, one that simplifies the data structure.
In this topic you have learnt that how the Represent form as presented, no interpretation, to yield starting point (UNF).Process of removing attributes in relations based on the concept of 1NF, 2NF and 3NF.
- UNF to 1NF define PK & remove repeating group
- 1NF to 2NF remove partial dependency.
- 2NF to 3NF remove transitive dependency.
Topic 5 - Programming in structured Query Language
LEARNING OUTCOME
By the end of this topics, you will be able to:
- Learn basic SQL statements for creating database structures
- Learn basic SQL statements for adding and manipulating data in database
- Learn basic SQL SELECT statements and options for processing a single table
IntroductionStructured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS). It is a standard language for storing, manipulating and retrieving data in databases. SQL developed by IBM in the late 1970’s, Endorsed and adopted by ANSI in 1992, Endorsed as a standard by the International Organization for Standardization (ISO).
SQL Statement CategoriesSQL Statement categories are as shown in the picture below:
Note : You may use online SQL compiler to complete the exercises on SQL coding.
Example of online SQL :
https://www.programiz.com/sql/online-compiler/
https://www.mycompiler.io/new/sqlTopic 6 - Database Administration
By the end of this topics, you will be able to:
- to understand the importance of database administration, concurrency control, security, back up and recovery
- to describe the use of locking and ACID transaction.
This topic introduces the database administration by describing the fundamental concepts, technology, and terminology used for multi-user database management.
It distinguishes between database administration and data administration. The latter is an organizational activity that typically sits high in the enterprise organization chart and may be concerned with much larger issues than the maintenance of a particular database. This topic is concerned with database administration, a more pedestrian function that occurs at a much lower level in the organization. Every multi-user database needs a database administrator. The administrator may only have a few tasks to perform and may work on them only a few hours a week, but someone needs to be given the responsibility for ensuring that the database administration tasks are accomplished on a timely basis. This is especially true for Internet technology databases where the users may be far away or even anonymous, and where the consequences of failures and mistakes will be difficult to correct.
- Concurrency control is important and sometimes seems obscure. If users do not learn the concepts and techniques, however, the DBMS will use default settings. These defaults may be perfect, but they also may be terrible. Thus, even though these issues can be ignored, their ramifications cannot be avoided.
- Security is becoming more and more
important. As implied in this topic, for
Internet applications, security tasks are accomplished by both the Web server
and the DBMS. Students should look into
these issues when they have had both Web server and database classes.
- The goal of this topic with regard to backup and recovery is to raise the students’ consciousness to these issues. They should get a general idea of the importance of backup and recovery and the general nature of backup and recovery tools and techniques.
Summary
In this topic you have learnt about Database administration, locking, deadlock, back up and recovery.
Topic 7 - Business Intelligence
By the end of this topics, you will be able to:
- Explain the components of business intelligence.
- Explain malicious software and ways to prevent it.
- Discuss the Denial of Service attack in software.
This topic explains the concept of business intelligence and a lot of developments to support the business operations. Business intelligence (B I) systems are information systems that assist managers and other professionals in the analysis of current and past activities and in the prediction of future events. It do not support operational activities, such as the recording and processing of orders. These are supported by transaction processing systems and support management assessment, analysis, planning and control
B I systems fall into two broad categories:
reporting systems that sort, filter, group, and make elementary calculations on operational data
data mining applications that perform sophisticated analyses on data; analyses that usually involve complex statistical and mathematical processing. The following image shows the structure and collaborations of resources and application of Business Intelligence to produce better decisions.
Summary- At the end of this lecture, it is understood that Business Intelligence (BI) systems are used to improve an enterprise's decision making by combining tools for gathering, storing, accessing, and analyzing business data.
- While traditional features for querying, reporting, and analytics have long been the core focus of these tools, BI has evolved in recent years to become comprehensive, enterprise-wide platforms, and newer trends, such as self-service BI, have helped to continue interest in this technology.
- BI is a combination of the tools and systems involved in an enterprise's strategic planning that aid in its analysis