Skip to main content

SQL Set Operators

SQL Set Operators   Traditional Set Operators: Union Compatibility:     Requirement for the traditional set operators.    Strong requirement.                         - Same number of columns.                         - Each corresponding column is compatible.                         - Positional correspondence.     Apply to similar tables by removing columns first. SQL UNION Example:           Example 1: Retrieve basic data about all university people.                     SELECT FacNo AS PerNo, FacFirstName                                      ...
Post a Comment

Schema

 

 
Schema

External Schemas:

  • also called subschemas.
  • Multiple schemas per database correspond to different views of the data.

Conceptual Schema:

  • describes all the entities, attributes, and relationships together with integrity constraints.
  • Only one schema per database.

Internal Schema:

  • a complete description of the internal model, containing the definitions of stored records, the methods of representation, the data fields, and the indexes and storage structures used.
  • Only one schema per database.

Mappings:

The DBMS is responsible for mapping between these three types of schema: 
        The DBMS must confirm that each external schema is derivable from the conceptual schema, and it must use the information in the conceptual schema to map between each external schema and the internal schema 

Types of mappings:

  1. conceptual/internal mapping.
  2. External/conceptual mapping.

Conceptual/Internal mapping:

    Enables DBMS to:
  • To find the actual record or combination of records in physical storage that constitute a logical record in the conceptual schema Together with any constraints to be enforced on the operations for that logical record. 
  • It also allows any differences in entity names, attribute names, attribute order, data types, and so on to be resolved.

External/Conceptual mapping:

    Map names in the user’s view to the relevant part of the conceptual schema.


Instances:

    Database Schema:

        Description of the database.
        Specified during design phase. 
        Remain almost static.

    Database Instance:

        Data in the database at any particular point in time.
        Dynamic Also called an intension (or state) of database.

Data Independence:

     Logical Data Independence:

        Refers to immunity of the external schemas to changes in the conceptual schema. 
        Conceptual schema to changes (e.g addition/removal of entities).
        Should not require changes to external schema or rewrites of application programs.

    Physical Data Independence:

        Refers to immunity of the conceptual schema to changes in the internal schema. 
        Internal schema changes (e.g different file organizations, storage structures, storage devices etc.) 
        Should not require change to conceptual or external schemas.

Data Independence & Three-Level Architecture




Our Official Website : Web Conquerors (https://www.webconquerors.com/) 

Want to get digital services? Contact US

Want to know about our services? Our Services

Labels:

Comments

Post a Comment

Popular posts from this blog

  d. Insert a new row in the Location table related to the Facility row in modification problem (c). The new row should have “Door” for the location name. Query Formulation + Result Snapshot Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services
Post a Comment
Read more

SQL Set Operators

SQL Set Operators   Traditional Set Operators: Union Compatibility:     Requirement for the traditional set operators.    Strong requirement.                         - Same number of columns.                         - Each corresponding column is compatible.                         - Positional correspondence.     Apply to similar tables by removing columns first. SQL UNION Example:           Example 1: Retrieve basic data about all university people.                     SELECT FacNo AS PerNo, FacFirstName                                      ...
Post a Comment
Read more

Correction For ERD

 d. For each consistency error in Figure 1, you should identify the consistency rule violated and suggest possible resolutions of the error. The ERD has generic names to help will concentrate on finding diagram errors rather than focusing on the meaning of the diagram. Answer:                Entity6 is violation of the Primary key rule. As there are 3 attributes from within Entity6 that are marked as primary key. Entity6 also violates the Identifying relationship rule, as there is no foreign key in the Entity6 table to identify the item from Entity7. Since this a weak entity type, the cardinality must be 1:1, which it is not. Entity2 is a violation of the Identifying relationship rule due to it not being a weak entity type when there is an identifying relationship. Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  ...
Post a Comment
Read more