Database Models and Systems

Introduction to Databases and DBMS

• Databases and related technology significantly impact computer use across various fields such as business, engineering, medicine, and education.
• A database is a structured collection of related data with implicit meaning; examples include names, phone numbers, and addresses stored electronically.
• Databases represent aspects of the real world and are designed, built, and populated for specific purposes with intended users.
• A DBMS is collection of programs enabling users to create and maintain databases, also facilitating defining, constructing, manipulating, and sharing databases.
• Defining a database involves data types, structures, and constraints specification which is stored as metadata in a database catalog or dictionary.
• Constructing a database means data storage on a storage medium which is controlled by the DBMS.
• Manipulating a database includes querying, updating to mimic real-world changes, and reporting.
• DBMS provides crucial functions like database protection from hardware/software malfunctions and security against unauthorized access.
• A database system comprises the database and the DBMS software.

Example: A University Database

• A University database contains data about students, courses, and grades
• It is organized into five files: STUDENT, COURSE, SECTION, GRADE_REPORT, PREREQUISITE.
• STUDENT file stores student details such as Name, Student_number, Class, and Major.
• Similarly, COURSE records include Course_name, Course_number, Credit_hours, and Department.
• Relationships exist within the database, linking student records to grades and courses to prerequisites.

Characteristics of the Database Approach

• The database approach differs from traditional file processing
• Traditional file processing sees each user defining and implementing specific files needed
• The database approach maintains a single data repository, with data elements defined once and used across multiple queries, transactions, and applications.
• File systems allow independent naming of data elements per application, whereas databases enforce unique data names.
• Key characteristics of the database approach include self-describing nature, insulation between programs and data, support for multiple data views, and multiuser transaction processing.

Self-Describing Nature of a Database System

• A DBMS catalog stores the definitions/descriptions of database structure and constraints.
• This stored information is called meta-data, containing details like file structure, data item types, storage format, and data constraints; useful for describing the primary database.

Insulation Between Programs, Data, and Data Abstraction

• In traditional file processing, changes to file structure require modifications to accessing programs.
• DBMS access programs are unaffected by structural changes, thus offering program-data independence.

Support of Multiple Data Views

• Databases accommodate multiple users, each needing a different perspective of the data
• A view may be a subset or derived virtual data not explicitly stored.
• Multiuser DBMS provide facilities for defining distinct views.

Sharing of Data and Multiuser Transaction Processing

• Multiuser DBMS allows simultaneous database access, essential for integrating data across multiple applications.
• To ensure correct updates, DBMS includes concurrency control software that manages multiple users updating the same data.

A Short Database History

• Databases have evolved from ancient records to modern systems, with advancements in information storage, indexing, and retrieval.
• In the 1960s, cost-effective computers and increased storage led to network (CODASYL) and hierarchical (IMS) models
• Low-level pointer operations were to link records; storage depended on data type. Adding fields required rewriting access schemes
• E.F. Codd proposed the relational model in 1970-72, decoupling logical database organization from physical storage which became standard since.
• The 1970s saw arguments over competing systems and theory-driven research - resulting in Ingres (UCB) using QUEL and System R (IBM) using SEQUEL.
• P. Chen introduced the Entity-Relationship (ER) model in 1976 for conceptual data models, allowing designers to focus on data and entities.
• Relational systems began being commercialized in the early 1980’s and fueling DB market growth for business.
• SQL became the intergalactic standard in the mid-1980s, with DB2 becoming IBM's flagship product.
• IBM’s PC development led to DB companies and products like RBASE 5000 and Paradox.
• The early 1990s marked an industry shakeout with fewer companies offering complex products at elevated prices.
• Client tools for applications like PowerBuilder and Oracle Developer emerged, establishing the client-server model.
• The mid-1990s saw rapid Internet/WWW growth, demanding remote access to legacy systems.
• The late 1990s had fueled Internet company investments and tools for Web/Internet/DB connectors, featuring Java Servlets and Oracle Developer
• Open-source, online transaction (OLTP), and online analytics (OLAP) started.
• In the early 21st century, decline of the Internet industry did not stop growth in DB applications using PDAs and POS transactions.
• IBM, Microsoft, and Oracle became leaders of western companies.
• Future will see handling analyze huge terabyte systems, using novel science databases, clickstream analysis, data warehousing/mining/marts, smart personalized shopping,
• XML with Java will likely overtake SQL successors to allow emergence of RDBMS, and mobile databases.

Database System Concepts and Architecture

• Data abstraction suppresses storage details and highlights essential features to improve understanding
• Data models provide concepts to describe database structure, operations, relationships, and constraints.

Categories of Data Models

• High-level conceptual data models have ideas close to user perception
• Low-level physical data models describe how data is stored, usually for computer specialists.
• Representational/implementation data models are understandable by end-users but retain organization within the computer for direction implementation
• Conceptual models use entities, attributes, and relationships. Attributes describe entities; relationships link them.

Schemas, Instances, and Database State

• Database schema is the database description specified during the design phase which is expected to frequently change.
• Schema diagrams display schemas; schema constructs define each object in the schema
• The actual data in a database at a particular moment is the database state/snapshot or extension.
• Every database has a current set of instances. The process of defining creates an empty state.
• A good schema is important and the DBMS stores schema constructs and constraints(meta-data) in catalogs for software referral.

The Three-Schema Architecture

• Goal: separate user applications and the physical database, defining schemas at three levels.

Three-Level Architecture

• The internal level has an internal schema, describing physical storage structure using a physical data model with comprehensive storage details and access paths.
• Conceptual level has a conceptual schema, describing the database's structure for users while hiding storage details - describing entities, data types, relationships, operations, and constraints, using a representational data model.
• Implementation conceptual schemas are based on conceptual schemas in high-level data models.
• The external/view level has many external schemas/user views, each describing a part of the database which user groups are interested in.
• External schemas are typically implemented using representational data models based on high-level data models.

Centralized and Client/Server Architectures for DBMS

Centralized Architecture

• Early architectures use mainframe computers for system functions, including DBMS operations, which use computer terminals without processing power.

Basic Client/Server Architecture

• The Client/server architecture was created for various equipment like PCs, workstations, file servers, printers, db servers.
• This design defines specialized servers such as file or print servers, so that client machines use server resources.
• Web and e-mail servers also fall under this category.
• Client machines have appropriate interfaces to use servers and local processing that is needed to run applications.
• Software programs and packages are stored of specialized servers to allow clients to access.

Two-Tier Client/Server Architecture

• The Client/server architecture is increasingly popular in commercial DBMS packages such as SQL for RDBMS to divide client and server. User interfaces and applications runs on client side while SQL query and transaction executes on the server.
• Open Database Connectivity (ODBC) provides API for client programs to call the DBMS as long as client and server have the correct software installed and set up.
• Client programs connects to RDBMSs an send query or request via ODBC which is processed by database at server sites, sent to the client.
• Software split over client and server system are two tiers to allow existing system simplicity and compatibility. The web then changed client and server to create the three-tier option.

Three-Tier and n-Tier Architectures for Web Applications

• Many web applications use three tier architecture adding an layer with the client and the database server.
• The intermediate layer, the middle tear, is known as application, the Web server, depending on application as an intermediary.
• The server stores business rules or procedures or constraints to get access to store database security by authenticating client credentials to access data.
• Clients have interfaces and some business rules. The server gets requests from the client, will process requests, sends commands to databases, partially passes process data from server to client in GUI format.
• Present information layers show entered data. The business, handles rules to user or DBMS before data pass. The lower level includes data management services, or even the separate website in database.
• Architecture have greater components divided with tiered applications that run certain processes or system independently and programming and data are throughout a network.

Data Modeling Using the Entity-Relationship ER Model

• Conceptual modeling is important in successful database apps.
• A database Application refers to the database and corresponding program that queries or updates.
• An ER model is a conceptual high-level design to design database tools and applications represented by ER diagrams.

Using High-Level Conceptual Data Models for Database Design

• Database process has these steps shown by simplified process. First is the "requirements collection and analysis", having database designers interview user prospective to understand them
• Specifying the known functional applications is valuable in parallel to data requirements.
• Consists of retrieve or updates.
• Making conceptual schema must collect all requirements and analyze them using ER. Schemas includes things in the high level models and are easier to understand.
• Designers must specify properties of data, don't focus on storage details, also communicate that data and it approach for concentrate properities.
• The approach gives access to communication on things with no technical users, or storage details. Therefore it is easy create a conceptual database design.
• Designers specify high-level analysis in functional models. Confirm conceptual all identifier functional needs and modify initial schemas if some requirements unmet
• Next level in database is real database using DBMS. The recent DM Ss relational or model transform using the high models into implementation. It is models known mapping.

An Example Database Application

• There is description database applicoton called COMPANY, serving to show ER models use in schema. So the database keeps tracks Company's projects, employee departments, and project designers.
• Suppose the designer provide a follow discribe with a COMPANY for which you need database:
• Department has organization, is named uniquely, the departments are kept uniquely, there employees and track date
• To keep many location from many departments
• Having controller of projects can include what has unique in the single location.
• Keep name Employees, Ssn, address, salary, sex, birth date. Must work of many project. keep all the work and keep Supervisor.
• Want to track keep employee such dependent. Keep the relationships.

Entity Types, Entity Sets, Attributes, and Keys

• ER model describe key attributes, data relationships, and attributes.
• The basic object is that is represented is called "entity". ER presents any in world with independent existence. To be entity have objects in the universe.
• In real physical or concept will keep it all attribute will describe employee, as an entity. May describe each one with its value.

Entities and attributes

• The object is represented by ER model as a entity, and attributes An employee has Name, Age, Address, Salary, The Employer has Name, Headquarters
• Attribute occur often in ER
• The attribute value is the combination of the value. A hierarchy for address can be given under street, number, apart number. Concat makes this useful.
• Attributes with single values are single valued with multiple has set of values. Example includes degree of an attribute. Example colors.
• You may have to add a minimum and maximum cap of different value bounds. Stored vers Derived show time with relationship when (or many) attributes correlate. You could use the the same attribute in many of other ways/entities (such as work in department).
• The null case show does have application value, and is required to be in address and name. You need created is a special null

Entity types and sets

• Types refer to something is a category and data usually groups a lot of data that is the sample; each of these that share similaries. Each entity has all it owns values.
• Entity type define and shows with to have that the same attribute. It describes with has name and all attribute lists. An entity has represented ER or entity.

Key Attributes of Entity Type

• When important attributes from the entity are keys or uniqueness is key about it. To identify entity of each set. Usually that attribute will be the entity key, that have unique identifier.

Value sets

• Sets will give the associated each attribute of values. Which means we can assign values to an inidibal entity. You can define a set between numbers.
• The the range that it will able to to define attribute the is set up by.

Initial Conceptual Design of the COMPANY Database

• The definition for the entity types for company for a model design. Several defenitions needed to be used, following will use 4 item to show it specs.

4 Entity Types

• Department has Name, Manager_start_date Locations. Key name unique.
• Project include Controlling Department. key name/unique and name.
• Employees will get a name and supervisor with set type in it.
• An entity has name and relationship (to the employee)

Relationship Types, Relationship Sets, Roles, and Structural Constraints.

• There a implicit relationships among the various entity type. whenever the one entity to the other. Example manager with employee. Supervisor with employee, attribute to employee.
• Relationship set define in set/associations. ER show them in straight diamond shapes, and rectangular boxes for entity.

Relationship type

• In data in one world relation they each will show way it in mini world that what is going to happen so consider for works. that related has some way in situations. Example works in the relation, to show.

Relationship

• A Relationship type is measure a by number entity to shows work for relationship. that two one is binary ternary type. Shows three different relation if this is in the data.

Constraints

• Relationship set often have the limits and each has corresponding number and each relation will set if so this is. This is the same with cardinality.
• Ratio will show amount participate in relation to entity. with the participation the existence is depend on the other and also the value. So you then make the cardinality maximum in the constraint.

Refining the ER Design for the COMPANY Database

• Define find database and it is related by attribute of the database type.
• Some data can not determined have is the ratio. Must further make structural constraints.
  • Manage ratio it relationship with employee and the participation depends it all by required user information.
  • Each work and the employee has total.
• Super and have type or the is some point will partial to that what has the relationship. And some one with has will some employee and to have.

Relationships

• Keep relationship and entity because some needs each some partial.

Relational Model: Concepts

• Relational shows a database a in list of that table. When relation view that table that data the a. key be there
• Introduce type for with the relations. The typically one be relationship with you may need to give list of types. key to identify values to data type. Formal terms a row as a tuple, and header a, data type a. Domain. Show terms and relation to it

Domain

• Data show to be in database with relation with its entity. Will show display. Show value how to connect things to make the entity.

• Null also used. Can be used by to describe all the state and the will have related in system at to be in point of the time. Those derive from constraints.

• The constrian on that for that by divided key, and must be by models that are not specified so it's implicit constraints. A. The table is defined a set of tuples that you can use to have. And every relation is distnict that there are what is distinct? means no tuples that have with the value with their all attributes.

Key

• Key with unique it all a field in data and have unique. A table can used be have that one set and it's a it values has no null

Foreign Key

• It combine of the column that use enforces the establish the the other data data with A You modify table you are that used the create that data in of the table. You then the with be constraint to that cannot null and that each have attribute and. will also of what that to that to state those table some has ways in. in also the will a of that employee a employee and with and. to a other data that will from The the of will a data table the a key that a to of. to is show for each have there

Relational Database Schema

• We display foreign keys using directed arcs that from the to will reference to to table and to make may is.

And attributes show with concept but also may is by have, and alter a that

Introduction

• Shows how relational models form the the database which will be very important. Each model has operation to update structure.
• The main set include will the of any base what base are very has the for some what which The for The base

SELECT and it's use

• Operation use a for Can be describe an operator A the is with clause A form

This lesson explores the evolution of database models, from legacy hierarchical systems to modern relational databases. It covers limitations, advantages, and the impact of SQL as a standard database language and also provides guidance on selecting the most effective approach for complex data relationships and RDBMS options.