Data Models, Schemas, and Instances
Data abstraction generally refers to the suppression of details of data organization and storage and highlighting of the essential features for an improved understanding of data. One of the main characteristics of the database approach is to support data abstraction so that different users may perceive data at their preferred level of detail
A data model – a collection of concepts that can be used to describe the structure of a database – provides the necessary means to achieve this abstraction.
High-level or conceptual data models provide concepts that are close to the way many users perceive data
low-level or physical data models provide concepts that describe the details of how data is stored in the computer
representational (or implementation) data models, which provide concepts that may be understood by end users but that are not too far removed from the way data is organized within the computer
An entity represents a real-world object or concept, such as an employee or a project that is described in the database.
An attribute represents some property of interest that further describes an entity, such as the employee’s name or salary.
A relationship among two or more entities, for example, a works-on relationship between an employee and a project.
database schema, which is specified during database design and is not expected to change frequently
A displayed schema is called a schema diagram
The data in the database at a particular moment in time is called a database state or snapshot or the current set of occurrences or instances in the database.
the descriptions of the schema constructs and constraints –is called the meta-data
The schema is sometimes called the intension, and a database state is called an extension of the schema
The Three-Schema Architecture
1.The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database
2.The conceptual level has a conceptual schema, which describes the structure of the whole database for a community of users
3.The external or view level includes a number of external schemas or user views.. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group
The process of transforming requests and results between levels are called mappings.
Centralized DBMS Architecture
Earlier architectures used mainframe computers to provide the main processing for all system functions, including user application programs and user interface programs, as well as DBMS functionality
The client/server architecture was developed to deal with computing environments in which a large number of PCs, workstations, file servers, printers, database servers, Web servers, and other equipment are connected via a network
A standard called Open Database Connectivity (ODBC) provides an application programming interface (API), which allows client-side programs to call the DBMS, as long as both client and server machines have the necessary software installed.
Many Web applications use an architecture called the three-tier architecture, which adds an intermediate layer(which could be the application server or web server depending on the application) between the client and the database server
Data Modelling Using the Entity-Relationship (ER) Model
database application refers to a particular database and the associated programs that implement the database queries and updates
diagrammatic notation associated with the ER model, known as ER diagrams
creating a conceptual schema for the database, using a high-level conceptual data model is called conceptual design
Entity Types, Entity Sets, Attributes, and Keys
Composite attributes can be divided into smaller subparts, which represent more basic with independent meanings. For example, the Address attribute of the EMPLOYEE entity can be subdivided into Street_address, City, State, and Zip, with the values ‘2311 Kirby,’ ‘Houston,’ ‘Texas,’ ‘77001.’
Attributes that are not divisible are called simple or atomic attributes
Most attributes have a single value for a particular entity; such attributes are called single-valued. For example, Age is a single-valued attribute of a person.
different persons can have different numbers of values for the College_degrees attribute. Such attributes are called multivalued.
For a particular person entity, the value of Age can be determined from the current (today’s) date and the value of that person’s Birth_date. The Age attribute is hence called a derived attribute and is said to be derivable from the Birth_date attribute, which is called stored attribute.
NULL can be used if we do not know the value of an attribute for a particular entity – for example, if we do not know the home phone number of ‘John Smith’.
An entity type defines a collection ( or set) of entities that have the same attributes.
An entity type usually has an attribute whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute
Weak Entity Type is an entity that does not have a key attribute
Each simple attribute of an entity type is associated with a value set (or domain of values)
Each entity type will have a collection of entities stored in the database called the entity set
Relationship Types, Sets, and Instances
Relationships of the same type are grouped or typed into a relationship type
Relationship Set is the current set of relationship instances represented in the database. The current state of a relationship
The degree of a relationship type is the number of participating entity types
A relationship type of degree two is called binary, and one of degree three is called ternary
The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in
The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type
SOME SQL QUERIES TO NOTE
CREATE statement, which can be used to create schemas, tables (relations), and domains (as well as other constructs such as views, and triggers)
The ALTER TABLE statement enables you to modify a table definition by altering, adding, or dropping columns and constraints or by disabling or enabling constraints and triggers.
The DROP TABLE statement removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.
The ADD keyword allows you to add a column to specific table
The SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables.
To try your knowledge on some carefully picked out questions click on this post
