Introduction to Database Systems Vol 1

Introduction to Database Systems: Volume 1

• This course is divided into two volumes, focusing on Database systems.
• The introductory chapter outlines the subject, aims and learning objectives, learning resources, and examination advice.

Route Map

• Volume 1 primarily addresses the relational model in both theoretical and practical contexts.
• Chapter 1 introduces the subject and provide exam advice.
• Chapter 2 describes the components of database environments, prevalent architectures, and the data model concept.
• Chapter 3 focuses on the relational model including relational data objects, relational operators, and data integrity aspects.
• Chapter 4 introduces ANSI SQL, emphasizing differences between versions and practical programming skills development.
• Chapter 5 covers database design, focusing on E/R modeling for conceptual design and normal forms for logical design.
• Volume 1 includes a sample examination questions appendix.
• Volume 2 covers advanced database system topics, like relational database management systems and alternative models.
• Chapter 2 in Volume 2 discusses security, data preservation, and database optimization.
• Chapter 3 in Volume 2 covers distributed architecture models for database systems.
• Chapter 4 in Volume 2 is dedicated to newer database systems approaches like deductive, object-oriented, NoSQL, and semantic web databases.
• Volume 2 includes both appendices with examination question answers and a dataset.
• A practical experiment on a live system is the easiest way to understand database systems.
• A dataset is provided on the VLE.
• The dataset's structure and data are useful in examples, particularly in Chapter 4 of Volume 1.

Key Terms

• The subject guide introduces vocabulary, concepts, and skills needed for the course examination.
• Each chapter has a Learning Outcomes section that outlines key terms and skills.
• These lists can be used to self-asses the understanding of the chapter material.

Subject Area Introduction

• Understanding database systems is an essential skill for computer scientists and information technologists.
• Database systems have evolved into a conventional subject but require theoretical and practical addressing due to continuously developing ideas.
• This subject has both a 'classic' relational model and a 'young' internet-based use component.

Syllabus

• Introduction to Database Systems includes motivation, storage systems, architecture, facilities, and applications.
• Database modeling covers basic concepts, E-R modeling, and schema deviation.
• Relational models cover algebra, and Structured Query Language (SQL) involves definitions, manipulations, access centers, and embedding.
• Physical design involves estimating workload, access time, logical I/Os, and distribution.
• Modern database systems cover extended relational and Object Oriented (OO) systems.
• Advanced database systems include active, deductive, parallel, distributed, and federated systems.
• Database functionality and services cover files, structures, access methods, transactions, concurrency control, reliability, and query processing.

Course Aims

• The course aims for an understanding of data storage and manipulation, introducing relational model theory and practice, emerging trends, ANSI SQL, activities and assignments to develop relational database systems.

Learning Objectives

• Learners will be able to explain needs for, and describe database systems and environments.
• Learners will be able to thoroughly describe the relational model.
• Learners will be introduced to ANSI SQL.
• Learners will be able to describe database design issues including conceptual design by E/R modeling, and logical design through normalization.
• Learners will be able to develop skills in relational database design and implementation.
• Learners will be presented with issues pertaining to security, recovery, and concurrency control.
• Learners will be introduced to distributed architectures, and newer approaches differing from the relational model.
• Learners will be able to describe links between databases and web technologies.

Learning Outcomes

• Students will be able to understand issues relating to database systems and know the relational model.
• Students will be able to analyze problems, synthesize requirements, and design corresponding relational databases with implementation and maintenance skills in Structured Query Language (SQL).
• Students will have awareness of alternative systems like web-scale, object databases, and deductive systems.
• Students will need to have in-depth knowledge in previously mentioned trends.

Learning Resources

• Each guide chapter has essential and further reading lists.
• Essential readings direct to required journal/textbook sections with choice between Date, and/or Connolly and Begg.
• It is advised to read recommended readings for both textbooks.
• Further reading provides pointers toward topic literature, and has associated notes.
• Extensive reading is enabled through virtual learning environment (VLE), and University of London Online Library access.

Essential Reading

• It is crucial to use the study resources available online, including VLE and Online Library.

Sample Examination Questions and Answers

• To help practice for the examination, some end-of-chapter sample questions have been included, with scope limited to the content of that chapter.
• Aim to spend no more than 45 minutes answering each sample question.
• After attempting the sample questions under timed conditions, check the appendix for answers.

Examination Advice

• Check the current Regulations for relevant examination information.
• Check the VLE for any forthcoming changes.
• Follow rubric instructions on the actual question paper.
• The exam requires answering four of five questions within 3 hours.
• Each question carries 25 marks, and may require knowledge beyond its topic.
• Before starting, read each question, and skip any element that the test taker doubts they can answer.
• Some questions have simple system description, and ask for a model using a relational model or SQL tables.
• Summarizing key terms and definitions is needed.
• When asked for an explanation or list of reasons, note the marks allocated to the question, so that the examinee provides answers accordingly.
• Practicing with past examination papers is the best way to prepare in the weeks before the examination.
• Check the VLE for the most up-to-date info.

Chapter Overview

• The course and volume 1 of the subject guide have been introduced, and aims and objectives of the course and practical aspects have been listed, along with reading and resources.

Learning Outcomes Reminder

• Having completed all essential readings and activities, the student should be able to understand main issues related to database systems, and have detailed knowledge about the relational model.
• Learners should be able to analyze specific problems, synthesize requirements, and develop a top-down design for a corresponding relational database.
• Learners should be able to implement and maintain a relational database in SQL.
• Learners should have awareness of alternative approaches/ models for database systems including: web-scale, object, deductive, knowledge-based systems, etc.
• Learners will have more in-depth knowledge in previously mentioned trends.

Databases: Basic Concepts

• Chapter 2 examines the basics of databases, including benefits and drawbacks, prior to discussing relational and data modeling.

Overview of Chapter 2 Aims

• To give what databases are, their components, the architecture of database systems, what a database management system (DBMS) is, data modeling theory and its context.

Learning Outcomes for Chapter 2

• Learners should be able to discuss file-based approach limitations, explain database approaches, and fully understand DBMSs.
• Learners should also be able to describe the SPARC architecture, discuss schemas and mappings, explain data independence, and the roles of data components, hardware, software, and users.
• Learners should be able to present the important DBMS features.
• Learners will be able to discuss the advantages and disadvantages of, and issues related to, distributed systems.
• Learners should be able to explain models and theories, within database systems' context.

Chapter 2: Essential Information

• C.J. Date's "An introduction to database systems," (8th edition), Chapters 1 & 2, and/ or Connolly and C.E. Begg's "Database systems: a practical approach to design implementation and management" (6th edition), Chapters 1 & 2.

Chapter 2: Further Reading

• Fundamentals of Database Systems, 7th edition., by Elmasri, R., and Navathe, S.B.

What is a Database?

• Common databases include Amazon, Facebook, YouTube, and iTunes.
• Users are obvious for online retailers, and less direct purpose, like keeping track of employees or tracking visitor ad activity.

DB Activity

• The database and structure may be obvious for a library catalogue or online retailer.
• They also serve less direct purposes, allowing companies to keep track of employees and supplies.
• The database also helps advertisers target visitors to web pages across different sites.
• Store data so databases help retailers with ad campaigns, etc.

Database System Qualification

• Ability to find (retrieve) data.
• Add (insert) new data.
• Delete unwanted data.
• change (update) data.
• A shoe shop's product information can be stored in database in the form of a table.

Shoe Shop Example

• The Vans Ferris range will be deleted once they are sold.
• Kaplan Mesh shoes won't sell well, so prices will be updated.
• Asics Gel Lite VI is released to add a new product.
• Databases have structure and content with dynamic content change, with less changeable structure.
• Structure is intension and changeable content is the extension.
• Databases are capable of storing a large amount of data.
• Database management is more than just managing data.

The Need for The Database Approach

• Any system that has something in common that distinguishes them from other software systems warrants a database approach.
• In appearance file systems are similar, but conceptually they are qualitatively different.
• Drawbacks of file-based approach to find database approach.

File-Based Systems Definition

• File-based systems are each a collection of applications managing their own data in ad hoc files, leading to de-centralized manipulation of records directly from persistent memory with limited sharing.

File-Based System Drawbacks

• Duplication occurs, where the same information is stored multiple times, which wastes space and can lead to inconsistencies if data isn't updated uniformly across all files.
• Separation and isolation occur when data is scattered across separate files owned by different departments, limiting access and integration for complex analyses.
• Program-data dependence means that each file has a designated program, leading to incompatible file formats and required modifications to applications when data structures change.
• Data modeling and its associated concerns are placed at a lower priority that functionality in file-based systems.

Databases and Database Management Systems (DBMS)

• Connolly and Begg define a database as a shared collection of logically-related data as part of the information system of an organization.
• A dataabase stores data, is defined and stored once, and is integrated with minimal redundancy for sharing among application programs.

Defining a DBMS

• A software system that provides built-in functionality for defining, accessing, and maintaining a database.
• The information, which stored in a database is self-describing.
• Data structure may change without affecting use of app.
• Data abstraction in programming is similar to data definition from app.

Tasks

• A DBMS performs housekeeping, provides powerful database development and exploitation tools to its user, and is needed for a clean set.
• Distinctions may be seen between file-based and databases, so data separation may have to occur with data use by a single department.

The Three-Level ANSI/SPARC Architecture

• Program-data independence is achieved in a database in two or more levels:
• Low-level: physical data support -High-level: logical structure of data
• Physical characteristics do not need to be considered.

Aims Formalized By ANSI/SPARC Architecture

• Users and application programs must be freed from considering elements of the data's physical representation, along with storage and accessibility details.
• It should be possible to adjust the data's physical representation with affecting users, within its structure that is sustained.
• A database incorporates all aspects required within a company, each user often just need access to pools of data.

Abstraction Levels

-The external level represents how individual users view the data. -The conceptual level describes how organizational data and structure will occur.

• The internal level represents the way data is physically, and what the system is likely to do.

Schemas and Mappings

• Description of the database is known as the intension.
• Raw data is the extension.
• Database schema consists of external, conceptual and internal layers.
• Separation of concerns results in mapping: external/conceptual and conceptual/internal.

Data Independence

• A key advantage provided.
• Physical data independence is when application programs are free of chagnes
• Logical data independence is when applications are free of changes with appropriate modification.

Database System Components

• Database environment consists of data, representing crucial information, software to manage/ process data, hardware to support, and users including both developers and deployers.

Data Classification

• Made into Primary/ fundamental and Derived/recalculated.
• Primary data has characteristics for being integrated and extensively shared.

Software Classification

• Presented by the Operating system (OS), DBMS, and application programs.
• The DBMS provides schema languages, etc.
• Applications formats/computations are by the DBMS.

Hardware Considerations

• Hardware and OS often grouped together as "platform."
• Details not needing to be focused on.
• Selection based off software, and operating systems.

Data Requirements

• Permanent storage space, temporary storage space, ample computational power, fast communication.
• Vendors provide configuration requirements.

Users

• Users can be categorized in data administrators, database administrators, application programers, and end users.
• The user term can mean many people.

DBMS and Database Languages

• A DBMS is the tool through which all database access occurs.
• Features: schema definition, data manipulation, data security, and data integrity.
• Application programs may be user-developed, or offered by the developer, and may be 4GL.
• The DBMS is referred to as the Server, whereas the apps are the Client, with their division giving rise to distributed processing.

Advantages of DataBase Systems

• Reduced redundancy, and thus data duplicaiton.
• Consistency is therefore easily kept due to the integration via the database approach.
• Data is more easily shared, due to centralization and restrictions.
• Data independece allows for protections of applications when in chagne.

Other Database Approach Benefits.

• Overall improved maintenence, and an integrity that allows specification of consraints.
• More detailed and coherent security restrictions can be developed, and standards enforced.
• Concurrent access more easily achieved, resulting in better recovery mechanisms.
• Scaling is less of a concern.

Database System Disadvantages

• Complexity and more complexity-driven implementations, in addition to harder implementation.
• The higher impact of a failure when the entire structure is centralized, and more performance issues than simply individual file-based solutions.

Database System Architectures

• Can occur in a client-server model, which a server, dedicated to the machine, has great demands for the hardware in order to run competently and at speed.
• Applications would also run on different client machines
• A distributed archicture also would be used, and can be implemented on several machines.

Date Models

_Data model is that at is at a conceptual level and is the focus of physical data independence, users work with data, and can be described in charaxteristics.

• The theory often revolves around relational models, consisting of concepts, operators, and rules in combining.
• The implementation of a model is database management system (DBMS).

Overview of Chapter 2

• The chapter explained the advantages, the defined benefits, the characterized file-based approaches, standards and components.

Learning Outcomes Reminder - Key Concepts

• Must be able to describe database independence, hardware software as a system component.

Other Outcomes

Must be able to:

• Discuss the limitations, and present important DBMS components.
• Address distributed systems, and their challenges.
• Define a mode for database connections

Next Chapter

The relational model and relational DBMSs

• Chapter three provides an introduction on concepts on how data will be added, or how a database's integrity will be checked.

Chapter 3 Aims

Chapter three addresses theory, relationships, relational components, operators, and integrality.

Learning Outcomes

• Must understand the language, the model used, the type.
• Must understand how to be able to make changes to models or relations/ what they do.

Further outcomes

• Know how to discuss key rules and functions.

Relational Models

• Models must be a theory with data and relations.

Relational DBMSs

• A relational DBMS supports relational data.

Relation to DDL

• Must follow two conditions: data values that are scalar, which means the structure cannot be broken.
• Information is best expressed in date values.