Database

• Business rule


  A statement that defines or constraints some aspect of the business organization. It is also intended to assert business structure or to control or influence the behavior of the business. It is the rule that prevent, cause, or suggest things to happen
• Types of business rules

  • Derivation
  • Structural assertion
  • Action assertion
• Business rules in a University set-up like Bulacan State University


  • Each STUDENT may enroll to only one PROGRAM
  • Each PROGRAM is enrolled by one or more STUDENT(s)
  • A STUDENT may complete any number of SUBJECT(s)
  • Each SUBJECT may be completed by any number of STUDENT(s)
  • Each EXAMINEE chooses exactly one COURSE
  • Each COURSE is chosen by at least one EXAMINEE(s)
• Business rule grammar


  <entity><minimum cardinality><relationship><maximum cardinality><entity>
• To find minimum and maximum cardinality


  Each\A\An <entity>><minimum cardinality><relationship><how many?> <entity>
• Entity-relationship (E-R) model


  A logical representation of data for an organization or for a business area. Normally represented in an entity relationship diagram (ERD) that uses graphic representations to model the database components. The three components are: the entity, the attributes and the relationship.
• Strong Entity


  • Entity types that exist independently of the other entity types. Majority of the entity types found in ERD diagram are considered strong entity types.
• Weak Entity


  • Entity types whose existence depends on some other entity type. A weak entity type has no business meaning in E-R diagram without the entity on which it depends, which is called its identifying owner or simply its owner.
• Attribute


  A property or a characteristic of an entity type that is of interest to the organization. Enumerated inside the rectangle after declaring the entity type name.
• Simple Attribute or Atomic Attribute

  • Attributes that cannot be broken down into simpler components.
• CREATE DATABASE


  The Create Database statement is used to create a new SQL database
• USE DATABASE


  It is used to switch from the default database master to preferred database
• Data Definition Language (DDL)


  Statements used to define the database structure
• CREATE TABLE Statement


  The CREATE TABLE statement is used to create a new table in a database
• CREATE TABLE


  • CREATE TABLE STUDENT( StudNo int not null primary key, LName varchar(15), FName varchar(15), Course varchar(5), YrLevel int);
• DROP TABLE Statement


  The DROP TABLE statement is used to drop an existing table in a database
• DROP TABLE


  DROP TABLE table_name;
• DROP TABLE


  • DROP TABLE STUDENT;
• DROP DATABASE Statement


  The DROP DATABASE statement is used to drop an existing SQL database
• DROP DATABASE


  DROP DATABASE databasename;
• DROP DATABASE


  • DROP DATABASE IT;
• ALTER Statement


  The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.
• ALTER TABLE - ADD Column


  ALTER TABLE table_name ADD column_name datatype;
• ALTER TABLE - ADD Column


  • ALTER TABLE STUDENT ADD TFee int;
• ALTER TABLE - DROP COLUMN


  ALTER TABLE table_name DROP COLUMN column_name;
• ALTER TABLE - DROP COLUMN


  • ALTER TABLE STUDENT DROP COLUMN FName;
• ALTER TABLE - ALTER/MODIFY COLUMN


  ALTER TABLE table_name MODIFY column_name datatype;
• ALTER TABLE - ALTER/MODIFY COLUMN


  • ALTER TABLE STUDENT MODIFY TFee decimal(6,2);
• Data Manipulation Language (DML)


  Statements used to manipulate the data in the database
• INSERT INTO Statement


  The INSERT INTO statement is used to insert new records in a table
• INSERT INTO


  INSERT INTO table_name VALUES (value1, value2, value3, ...);
• INSERT INTO


  • INSERT INTO STUDENT VALUES(20191254, 'Castro', 'Macy', 'BSIT', 3);
• UPDATE Statement


  The UPDATE statement is used to modify the existing records in a table
• UPDATE


  UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
• UPDATE


  • UPDATE STUDENT SET YrLevel=2 where StudNo=20191254;

  UPDATE STUDENT SET TFee=4378.75 where StudNo=20191254;

  UPDATE CUSTOMER SET ContactName='Juan' WHERE Country='Mexico';
• UPDATE Multiple Records


  The WHERE clause determines how many records will be updated
• SELECT Statement


  The SELECT statement is used to select data from a database
• SELECT


  SELECT column1, column2, ... FROM table_name;
• SELECT


  • SELECT StudNo, LName FROM STUDENT;

  SELECT * FROM STUDENT;
• DELETE Statement


  The DELETE statement is used to delete existing records in a table