Skip to main content

Posts

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

Database Create Statements

University Database Create Statements :   DROP TABLE Enrollment; DROP TABLE offering; DROP TABLE Student; DROP TABLE Course; DROP TABLE Faculty;     -------------------- Student ------------------------   CREATE TABLE Student ( stdNo char(11) not null, stdFirstName varchar(30) not null, stdLastName varchar(30) not null, stdCity varchar(30) not null, stdState char(2) not null, stdZip char(10) not null, stdMajor char(6), stdClass char(2), stdGPA decimal(3,2), CONSTRAINT StudentPk PRIMARY KEY (StdNo) );     -------------------- Course ------------------------   CREATE TABLE Course ( CourseNo       char(6) not null, crsDesc        varchar(50) not null, CrsUnits       integer, CONSTRAINT CoursePK PRIMARY KEY (CourseNo) );     -------------------- Faculty ---------------------   CREAT...
Post a Comment
Read more

ER Diagram

  ER Diagram E-R Diagram description: • Student : stud-Id, last-Name, first-Name, major, credits                 – Each student has a unique id and has at most one major. • Department :  dept-Code, dept-Name, office                 – Each department has a unique code and a unique name, and that each department has one office designated as the departmental office. • Faculty : fact-Id, last-Name, first-Name, rank                 – fact-Id is unique and that every faculty member must belong to department. One faculty member in each department is the chairperson. • Class : class-Number, schedule, room – class-Number consists of dept-Code, course-Number, section. • Textbook : isbn, author, title, publisher                 – A book can have multiple authors. • Text...
Post a Comment
Read more

Entity

Entity • Object that exists and that can be distinguished from other objects  • Can be person, place, event, object, concept in the real world  • Can be physical object or abstraction  • Entity instance is a particular person, place, etc.  • Entity type is a category of entities  • Entity set is a collection of entities of same type-must be well-defined  • In E-R diagram, rectangle represents entity set. Data Modeling Concepts: Entity:      Entity instance – a single occurrence of an entity. Data Modeling Concepts: Degree: A ssociative entity – an entity that inherits its primary key from more than one other entity (called parents). Each part of that concatenated key points to one and only one instance of each of the connecting entities. Attributes: • Defining properties or qualities of entity type. • Represented by oval on E-R diagram. • Domain – set of allowable values for attribute.         ...
Post a Comment
Read more

Entity Relationship Model

Entity Relationship Model      Data modeling:                  A technique for organizing and documenting a system’s data. Sometimes called database modeling.       Entity relationship diagram (ERD):                A data model utilizing several notations to depict data in terms of the entities and relationships described by that data.      Purpose of E-R Model: Facilitates database design. Express logical properties of mini-world of interest within enterprise - Universe of Discourse. Conceptual level model. Not limited to any particular DBMS. E-R diagrams used as design tools. A semantic model – captures meanings.      Symbols used in E-R Diagram: Entity – rectangle   Attribute – oval Relationship – diamond Link - line         ER diagram: Our Official Website :  Web Conquerors  (https...
Post a Comment
Read more

DML | Data Manipulation Language

DML | Data Manipulation Language                DML(Data Manipulation Language) : used for adding (inserting), deleting and modifying (updating) data in a database                     • Insert                 • Update                 • Delete INSERT INTO :                INSERT INTO table_name (column1, column2, column3, ...)                VALUES (value1, value2, value3, ...);                INSERT INTO table_name                VALUES (value1, value2, value3, ...);                create table mytab3(                ...
Post a Comment
Read more

DDL | Data Definition Language

DDL | Data Definition Language           DDL or Data Definition Language actually  consists of the SQL commands that can be used to define the database  schema.                •CREATE – is used to create the database or its objects (like table, index,  function, views, store procedure and triggers).                •DROP – is used to delete objects from the database.                •ALTER  –  is used to alter the structure of the database.                •TRUNCATE – is used to remove all records from a table, including all spaces  allocated for the records are removed.                •COMMENT – is used to add comments to the data dictionary.                •R...
Post a Comment
Read more

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: conceptual/internal mapping. External/conceptual mapping. Conceptual/Internal mapping:      Enables DBMS to: To fi...
Post a Comment
Read more