What are the 4 DDL command statements most commonly used? Describe each briefly.
SELECT – to query data in the database.
INSERT – to insert data into a table.
UPDATE – to update data in a table.
DELETE – to delete data from a table.
When and why would you use a “LIKE” clause?
The “LIKE” clause would be used when specified portions of character strings is needing to be selected.
Because it needs specific information is needing to be selected.
Describe a scenario of when an INSERT and DELETE commands are used.
A scenario for “INSERT” would be to add a book record to a publisher’s Authors table
A scenario for “DELETE” would be if an employee was fired from their job and the company needed to delete their information.
Create an Employee table in a new database Company.
What is SQL? Name two DBMS that you have came across that can uses SQL?
SQL is a database language designed for managing data held in a relational database management system.
I’ve come across mySQL & Microsoft SQL server
What is the purpose of column constraint? Name the common optional column constraints available.
The purpose of a column constraint is to initialize a value for a new record.
The optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT and AUTO_INCREMENT.
Using the data information provided in the next 3 slides, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.
- tblLevels
- Level – auto increment PK
- ClassName – text 20 – nulls are not allowed
CREATE TABLE tblLevel
(
Level NOT NULL AUTO_INCREMENT,
ClassName CHAR(20) NOT NULL,
CONSTRAINT tblLevel_PK PRIMARY KEY(Level)
);
- tblPool
- Pool – auto increment PK
- PoolName – text 20 – nulls are not allowed
- Location – text 30
CREATE TABLE tblPool
(
Pool NOT NULL AUTO_INCREMENT,
PoolName CHAR(20) NOT NULL,
Location CHAR(30) NULL,
CONSTRAINT tblPool_PK PRIMARY KEY(Pool)
);
- tblStaff
- StaffID – auto increment PK
- FirstName – text 20
- MiddleInitial – text 3
- LastName – Text 30
- Suffix – text 3
- Salaried – bit
- PayAmount – numeric
CREATE TABLE tblStaff
(
StaffID NOT NULL AUTO_INREMENT,
FirstName CHAR(20) NULL,
MiddleInitial CHAR(3) NULL,
LastName CHAR(30) NULL,
Suffix CHAR(3) NULL,
Salaried BIT NULL,
PayAmount NUMERIC NULL,
CONSTRAINT tblStaff_PK PRIMARY KEY(Staff)
);
- tblClasses
- LessonIndex – auto increment PK
- Level – Integer FK
- SectionID – Integer
- Semester – TinyInt
- Days – text 20
- Time – datetime (Formatted for time)
- Pool – Integer FK
- Instructor – Integer FK
- Limit – TinyInt
- Enrolled – TinyInt
- Price – numeric
CREATE TABLE tblClasses
(
LessonIndex NOT NULL AUTO_INCREMENT,
FOREIGN KEY (Level) REFERENCES tblLevel,
SectionID Int NULL,
Semester TINYINT NULL,
Days CHAR (20) NULL,
Time DateTime NULL,
FOREIGN KEY (Pool) REFERENCES tblPool,
FOREIGN KEY (Instructor) REFERENCES tblStaff,
Limit TINYINT NULL,
Enrolled TINYINT NULL,
Price NUMERIC NULL,
CONSTRAINT tblClasses_PK PRIMARY KEY(Classes)
);
- tblEnrollment
- LessonIndex – Integer FK
- SID – Integer FK (LessonIndex and SID) Primary Key
- Status – text 30
- Charged – bit
- AmountPaid – numeric
- DateEnrolled – datetime
CREATE TABLE tblEnrollment
(
LessonIndex INT NOT NULL,
SID INT NOT NULL,
Status CHAR(30) NULL,
Charged BIT NULL,
AmountPaid NUMERIC NULL,
DateEnrolled DateTime NULL,
);
- tblStudents
- SID – auto increment PK
- FirstName – text 20
- Middle Initial – text 3
- LastName – text 30
- Suffix – text 3
- Birthday – datetime
- LocalStreet – text 30
- LocalCity – text 20
CREATE TABLE tblStudents
(
SID NOT NULL AUTO_INCREMENT,
FirstName CHAR(20) NULL,
Middle Initial CHAR(3) NULL,
LastName CHAR(30) NULL,
Suffix CHAR(3) NULL,
Birthday DateTime NULL,
LocalStreet CHAR(30) NULL,
LocalCity CHAR(20) NULL
CONSTRAINT tblStudents_PK PRIMARY KEY(Students)
);
Create the table shown here in mySQL Server. Show the statements you used and include a snapshot of the final table structure.
Describe the waterfall model. List the steps.
It is a strict sequence of steps where the output of one step is the input to the next. Each step has to be completed in order to move on to the next.
- Establishing Requirements
- Statement of requirements
- Analysis
- System Specification
- Design
- Design Document
- Implementation
- Initial System
- Testing
- Released System
- Maintenance
What does the acronym SDLC mean, and what does an SDLC portray?
Software Development Life Cycle (SDLC)
It portrays: Planning, Implementation, Testing, Documentation, Deployment, Maintaining
What needs to be modified in the waterfall model to accommodate database design?
- Establishing Requirements
- Data Requirements
- Data analysis
- Conceptual Data Model
- Database design
- Logical Schema
- Implementation
- Initial Schema and database
- Testing
- Released schema and database
- Maintenance
Provide the iterative steps involved in database design.
- Use a relational representation of the conceptual data model to give a set of tables for a first-cut database design.
- Do the tables represent the data in an acceptable way according to the given criteria for usability, efficiency, and so on?
- If yes, define constituents of schema for each table such as:
- columns
- primary keys
- foreign keys
- constraints
- Implementation
- If no, flex tables to produce second cut design
- Ask the question again until the answer is yes.
What is normalization?
The branch of relational theory that provides design insights.
When is a table in 1NF?
Only single values are permitted at the intersection of each row and column, hence, there are no repeating groups.
When is a table in 2NF?
Only if the Primary Key is made from a single attribute.
When is a table in 3NF?
All transitive dependencies must be removed; a non-key attribute may not be functionally dependent on another non-key attribute.
Fill in the blanks:
Denormalization produces a lower normal form.
Any attribute whose value determines other values within a row is called a functional dependency.
An attribute that cannot be further divided is said to display atomic attribute.
Granularity refers to the level of detail represented by the values stored in a table’s row.
A relational table must not contain repeating groups.
Name the two types of integrity constraints and provide an example of each.
Entity integrity. Example: In an EMPLOYEE table, Phone cannot be a primary key because not everyone has a telephone.
Referential integrity: Example: In a Customer/Order database, there has to be one valid reference in each table that links the together.
In your own words explain and show some examples how business rules work.
Business rules are intended to assert business structure or to control or influence the behavior of the business. Example: “no credit check is to be performed on return customers.”
Describe cardinality and connectivity.
Cardinality – Describes the relationship between two data tables.
Connectivity – The relationship between two tables.
Explain the different between these 2 relationships: optional and mandatory.
The difference is in an optional relationship, the parent table does not need to have a corresponding child table occurrence while the mandatory relationship requires a corresponding entity occurrence.
How can such anomalies be eliminated? What three data anomalies are likely to be the result of data redundancy?
The anomalies below can be eliminated by ensuring the tables are normalized which requires the understanding functional dependencies.
Insertion Anomaly
Update Anomaly
Deletion Anomaly
GWDA372 