Database Architecture, Data Models, and Relational Algebra
**i) View of Data:**A database has multiple views to cater to different users.It consists of 3 levels:*Internal Level (Physical View):*Defines how data is physically stored on hardware.*Conceptual Level (Logical View):*Represents the entire database structure without storage details.*External Level (User View):*Provides customized views of data for different users,hiding unnecessary details.**ii) Data Abstraction:**Data abstraction simplifies database complexity by hiding lower-
level details.It has 3 levels:*Physical Level:*Deals with how data is stored physically (files,indexes).*Logical Level:*Shows relationships between data without storage details.*View Level:*Provides user-specific views,hiding both logical & physical complexities.
Iii) Instances & Schemas:
Schema:*The overall design or structure of a database,which defines tables,attributes,& relationships.It remains unchanged.*Instance:*The actual data stored in the database at a specific time.Instances change frequently as data is updated.
Relational Algebrais a formal query language used to retrieve & manipulate data stored in relational databases.It provides a set of operations that take 1 or more relations(tables)as input & produce a new relation as output.These operations help in querying the database efficiently.
Basic Operations in Relational Algebra:
1.**Selection(σ):**Retrieves rows(tuples)that satisfy a given condition.-Example:σ(Age>25)(Employee)→Selects employees older than 25.
2.**Projection(π):**Retrieves specific columns(attributes)from a relation.-Example:π(Name,Age)(Employee)→Displays only names & ages.
3.**Union(∪):**Combines 2 relations with the same attributes,removing duplicates
4.**Intersection(∩):**Returns common tuples from 2 relations
5.**Difference(-):**Retrieves tuples from the 1st relation that R not in the 2nd
6.**Cartesian Product(×):**Combines every tuple of 1 relation with every tuple of another
7.**Join(⨝):**Combines related tuples from 2 relations based on a condition
a)Different Data Models:Adata modeldefines how data is structured,stored,& manipulated in a database.The main types of data models
R:
1.**Hierarchical Model:**Organizes data in a tree-like structure with parent-child relationships.Each parent can have multiple children,but each child has only 1 parent.Example:XML data representation.
2.**Ne2rk Model:**Uses a graph structure where a record can have multiple parent & child records.It is more flexible than the hierarchical model.
Example:Integrated Data Store(IDS).
3.**Relational Model:**Represents data in tables(relations)with rows(tuples)& columns(attributes).It uses keys & constraints for data integrity.Example:MySQL,PostgreSQL.
4.**Object-Oriented Model:**Combines object-oriented programming principles with database systems.Data is stored as objects,supporting inheritance & encapsulation.Example:MongoDB,ObjectDB.
5.**Entity-Relationship(ER)Model:**Uses entities,attributes,& relationships to design databases visually before implementation.
b)ER Model & Basic Symbols:TheER Modelrepresents real-world data using entities(objects),attributes(properties),& relationships(associations).
-**Entity:**Represents real-world objects(e.G.,Student,Employee).**Symbol:**Rectangle
-**Attribute:**Represents properties of an entity(e.G.,Name,Age).**Symbol:**Oval
-**Relationship:**Shows associations between entities(e.G.,”Enrolled”between Student & Course).**Symbol:**Diamond
The ER model simplifies database design before converting it into a relational schema.
a) Levels of Abstraction:Database abstraction levels hide complexity & provide different views of data. There R 3 levels:
1. Physical Level: The lowest level, defining how data is physically stored on disks using data structures like indexes, hashing, & file organization.
2. Logical Level: The middle level, describing data structure, relationships, & constraints without storage details. It ensures data independence.
3. View Level: The highest level, providing user-specific views by hiding unnecessary data. It helps in security & simplifies database access.
b)A view is a virtual table that represents a subset of a database query. It does not store data but fetches results dynamically. Views enhance security by restricting direct table access.
To Alter a View: Modify its definition using:
CREATE OR REPLACE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
To Destroy a View: Remove it using:
DROP VIEW view_name