Microsoft SQL Server 2014 Data Definition Language (DDL) and Database Management

Core Learning Objectives

Identify Steps in Table Management: Recognize and systematically execute the necessary procedures to manage database tables.
Understand Data Definition Language (DDL): Master the core principles, syntax, and operational capabilities of DDL within Microsoft SQL Server 2014.
Execute Database Operations: Perform database table operations including creating, modifying, deleting structures, and populating data using structured query commands.

Data Definition Language (DDL) Fundamentals

Definition: Data Definition Language (DDL) is a standard set of SQL commands used to define, alter, and manage database structures and schemas.
Scope of Objects: DDL commands act directly upon database structures, including databases, tables, views, functions, and user entities.
Four Basic DDL Commands:
- USE: Selects an active database within the SQL schema.
- CREATE: Constructs new database objects such as tables, views, and functions.
- ALTER: Modifies the existing structure of a database object.
- DROP: Deletes existing objects from the database entirely.

Core DDL Commands and Syntaxes

USE Command

Purpose: Selects an existing database in the SQL schema to make it the active database context for subsequent queries.
Syntax:

  USE [Database Name]
&nbsp;&nbsp;```
* **Example**:

sql USE Mapagpunyagi_FirstName ```

CREATE Command

Purpose: Creates database objects, specifically tables, views, and functions.
Syntax for Creating a Table:

  CREATE TABLE [table name] (
      [column name] [data type] [null | not null],
      [column name] [data type] [null | not null],
      [column name] [data type] [null | not null]
  )
&nbsp;&nbsp;```
* **Component Breakdown**:
  * **CREATE TABLE**: The DDL command specifying the creation of a table object.
  * **[table name]**: The identifier assigned to the new table.
  * **Column name**: The identifier for an individual field within the table.
  * **Data type**: Defines the type of data the specified column can store (e.g., `int`, `nvarchar(50)`, `decimal(5,2)`).
  * **[null | not null]**: An optional constraint specifying whether a column accepts `NULL` values or requires mandatory entries.
* **Example Code**:

sql CREATE TABLE studentprofile ( Student_ID int null, Student_name nvarchar(50) not null, Student_address nvarchar(50) null, Average decimal(5,2) ) ```

Design View Representation:
- Student_ID: Data Type int, Allow Nulls = Yes (NULL)
- Student_name: Data Type nvarchar(50), Allow Nulls = No (NOT NULL)
- Student_address: Data Type nvarchar(50), Allow Nulls = Yes (NULL)
- Average: Data Type decimal(5, 2), Allow Nulls = Yes (NULL)
Datasheet View / Edit Top 200 Rows Dataset:
- Entry 1: Student_ID = 1001, Student_name = Juan Dela Cruz, Student_address = Bahrain, Average = 90.75
- Entry 2: Student_ID = 1002, Student_name = John Javellana, Student_address = Manila, Average = 90.12
- Entry 3: Student_ID = 1003, Student_name = Mark Luis, Student_address = Manila, Average = 100.72 (also recorded as 100.77)
- Entry 4: Student_ID = 1004, Student_name = John Estrada, Student_address = Bahrain, Average = 999.99

ALTER Command

Purpose: Modifies the structure of an existing database table without destroying the existing data object.
Adding Columns:
- Syntax: sql ALTER TABLE [table name] ADD [column name] [data type] [null | not null], [column name] [data type] [null | not null];     
- Example: sql ALTER TABLE studentprofile ADD username nvarchar(50);     
- Design View Impact: Adds username (nvarchar(50), Allow Nulls = Yes).
- Datasheet View Impact: Existing records display NULL for the new username column.
Dropping Columns:
- Syntax: sql ALTER TABLE [table name] DROP COLUMN [column name];     
- Example: sql ALTER TABLE student_info DROP COLUMN Student_address;     
Altering/Modifying Column Attributes:
- Syntax: sql ALTER TABLE [table name] ALTER COLUMN [column name] [data type];     
- Example: sql ALTER TABLE student_info ALTER COLUMN Student_ID int NOT NULL;

DROP Command

Purpose: Permanently deletes existing objects (such as tables) from the database schema.
Syntax:

  DROP TABLE [table name]
&nbsp;&nbsp;```
* **Example**:

sql DROP TABLE student info ```

Command Scramble Reference Key

RODP $\rightarrow$ DROP (Scramble Reference ID: 995 / 95)
EARLT $\rightarrow$ ALTER (Scramble Reference ID: 996)
EAECRT $\rightarrow$ CREATE (Scramble Reference ID: 95)
SEU $\rightarrow$ USE
YNSATX $\rightarrow$ SYNTAX (Scramble Reference ID: 95)

Assessment Schema and Key

Activity Table Schema Specifications (Pages 72–76)

student_number: nvarchar(50), Allow Null: No
SLastname: nvarchar(50), Allow Null: No
SFirstname: nvarchar(50), Allow Null: No
SMiddlename: nvarchar(50), Allow Null: Yes
Student contact number: nvarchar(50), Allow Null: No
SAddress1: nvarchar(50), Allow Null: No
SAddress2: nvarchar(50), Allow Null: No
Guardian: nvarchar(50), Allow Null: No
Guardianaddress: nvarchar(50), Allow Null: No
Guardianphone: Int(4), Allow Null: No

Activity Answer Key

A or D
D
A
C
A
B
B
A
C
D

Student Information System Database Specifications (PeTa 1.1)

Database Task Name: Performance Task 1.1 (PeTa 1.1) - Pages 66 to 71
Database Title: Student Information System Database

Table Schemas

Table 1: `studentprofile` Schema

Studentnumber: nvarchar(50)
SLastname: nvarchar(50)
SFirstname: nvarchar(50)
SMiddlename: nvarchar(50)
[Student contact number]: nvarchar(50)
SAddress1: nvarchar(50)
SAddress2: nvarchar(50)
Guardian: nvarchar(50)
Guardianaddress: nvarchar(50)
Guardianphone: nvarchar(50)

Table 2: `teacherprofile` Schema

Subjectcode: nvarchar(50)
Teachercode: nvarchar(50)
TFname: nvarchar(50)
TLname: nvarchar(50)
TAddress: nvarchar(50)
TPhone: nvarchar(50)
LoadUnit: int (Allow Nulls: NOT NULL)

Table 3: `student_section` Schema

Studentnumber: nvarchar(50)
Section: nvarchar(50)
Room: nvarchar(50)

Table 4: `subject_profile` Schema

Subjectcode: nvarchar(50)
Subjectdescription: nvarchar(50)
[lec units]: int

Master Datasets

Dataset for `studentprofile` Table

Row 1:
- Studentnumber: 1001
- Slastname: Mapanoo
- SFirstname: Ma. Eliza
- SMiddlename: Dulay
- Student contact number: 09175021234
- SAddress1: Carmona, Cavite
- SAddress2: Binan, Laguna
- Guardian: Merlin L. Mapanoo
- Guardianaddress: Binan, Laguna
- Guardianphone: 09175023456
Row 2:
- Studentnumber: 1002
- Slastname: Flores
- SFirstname: Tyrone
- SMiddlename: Nyte Catag
- Student contact number: 09177419821
- SAddress1: San Pablo City, Laguna
- SAddress2: Alaminos, Laguna
- Guardian: Monica C. Flores
- Guardianaddress: San Pablo City...
- Guardianphone: 09177418732
Row 3:
- Studentnumber: 1003
- Slastname: Cataag
- SFirstname: Andrei Jacob
- SMiddlename: Bondoy
- Student contact number: 09236579841
- SAddress1: Lipa City, Batangas
- SAddress2: San Pablo City
- Guardian: Joy B. Cataag III
- Guardianaddress: Lipa City, Batan..
- Guardianphone: 09233246512
Row 4:
- Studentnumber: 1004
- Slastname: Mapanoo
- SFirstname: Nevin Joshua
- SMiddlename: Laurito
- Student contact number: 09392358909
- SAddress1: Sta. Rosa, Laguna
- SAddress2: Carmona, Cavite
- Guardian: Mariecris L Infante
- Guardianaddress: Binan, Laguna
- Guardianphone: 09175023457
Row 5:
- Studentnumber: 1005
- Slastname: Infante
- SFirstname: Nell Jonard
- SMiddlename: Mapanoo
- Student contact number: 09272358909
- SAddress1: Sta. Rosa, Laguna
- SAddress2: Carmona, Cavite
- Guardian: Mariecris L. Infante
- Guardianaddress: Binan, Laguna
- Guardianphone: 09175023458

Dataset for `teacherprofile` Table

Row 1: Subjectcode: MATH101 | Teachercode: ALMMAR | TFname: Mario | TLname: Almeda | TAddress: Calamba, Laguna | TPhone: 09171234567 | LoadUnit: 15
Row 2: Subjectcode: ENGLISH101 | Teachercode: CALEDG | TFname: Edgar | TLname: Calero | TAddress: Lipa, Batangas | TPhone: 09123456789 | LoadUnit: 15
Row 3: Subjectcode: FILIPINO101 | Teachercode: BRITER | TFname: Teresa | TLname: Briones | TAddress: Calauan, Laguna | TPhone: 09234567890 | LoadUnit: 15
Row 4: Subjectcode: LANGUAGE101 | Teachercode: NUQLYD | TFname: Lydia | TLname: Nuque | TAddress: Tiaong, Quezon | TPhone: 09326540987 | LoadUnit: 15
Row 5: Subjectcode: MAPEH101 | Teachercode: ORCMIC | TFname: Michael | TLname: Orcullo | TAddress: Sta. Rosa, Laguna | TPhone: 09189638521 | LoadUnit: 12
Row 6: Subjectcode: READING101 | Teachercode: GONLEI | TFname: Leilani | TLname: Gonzales | TAddress: Cabuyao, Laguna | TPhone: 09192587410 | LoadUnit: 15
Row 7: Subjectcode: RELIGION101 | Teachercode: TANMEL | TFname: Melissa | TLname: Tan | TAddress: San Pablo City, Laguna | TPhone: 09209876521 | LoadUnit: 9
Row 8: Subjectcode: SCIENCE101 | Teachercode: AQULIN | TFname: Linda | TLname: Aquino | TAddress: Candelaria, Quezon | TPhone: 09169516985 | LoadUnit: 15
Row 9: Subjectcode: SIBIKA101 | Teachercode: FLOVER | TFname: Vergilio | TLname: Flores | TAddress: San Pablo City, Laguna | TPhone: 09063215874 | LoadUnit: 12

Dataset for `student_section` Table

Row 1: Studentnumber: 1001 | Section: SHS - G8 | Room: 306
Row 2: Studentnumber: 1002 | Section: BHS - G8 | Room: 307
Row 3: Studentnumber: 1003 | Section: BHS - G8 | Room: 307
Row 4: Studentnumber: 1004 | Section: SHS - G8 | Room: 306
Row 5: Studentnumber: 1005 | Section: SHS - G8 | Room: 306
Row 6: Studentnumber: 1006 | Section: BHS - G8 | Room: 307
Row 7: Studentnumber: 1007 | Section: SHS - G8 | Room: 306

Dataset for `subject_profile` Table

Row 1: Subjectcode: ENGLISH101 | Subjectdescription: English Communication and Skills | lec units: 3
Row 2: Subjectcode: FILIPINO101 | Subjectdescription: Panitikan | lec units: 3
Row 3: Subjectcode: LANGUAGE101 | Subjectdescription: Language | lec units: 3
Row 4: Subjectcode: MAPEH101 | Subjectdescription: Music, Art, and PE | lec units: 3
Row 5: Subjectcode: MATH101 | Subjectdescription: Mathematics | lec units: 3
Row 6: Subjectcode: READING101 | Subjectdescription: Reading Comprehension | lec units: 3
Row 7: Subjectcode: SCIENCE101 | Subjectdescription: Science and Health | lec units: 3
Row 8: Subjectcode: SIBIKA101 | Subjectdescription: Sibika at Kultura | lec units: 3