Nội Dung QUATER 3

Admin Sun Apr 20, 2008 11:49 pm

QUATER 3: Designing and Implementing Relational Databases

Module 1 -> Relational Database Design (24 hrs – 3 cycles)

 Define a Database Management System (DBMS)
 Identify the different DBMS users
 Describe the need for a database system
 Define the architecture of a DBMS in terms of:
o External level
o Conceptual level
o Internal level
 Identify the functional components of a DBMS
 Identify the need for database planning
 Identify the stages in the Database Development Life Cycle (DDLC)
 Identify the effects of poor database planning and design
 Describe the types of data models
 Create an entity-relationship model
 List the types of relationships between entities
 Define a Relational Database Management System (RDBMS)
 Describe the operators that work on relations
 Map an E/R Diagram to Tables in relation to the following:
o Regular entities
o Attributes
o Relationships
o Weak entities
o Subtypes and supertypes
 Identify keys
 Define Specialization and Generalization
 Describe the Top-down and Bottom-up approach
 Describe data redundancy
 Describe the first, second, and third normal forms
 Describe the Boyce-Codd Normal Form (BCNF)
 Appreciate the need for denormalization

Module 2 -> Implementing Relational Database Design (72 hrs – 9 cycles)
Retrieving and Maintaining Data (24 hrs – 3 cycles)

 Identify the features of client/server architecture
 Identify the input requirements for a query
 Plan and create a format for the query output
 Identify and use sources of data for a query
 Identify and use different types of queries
 Use the SELECT statement with logical operators
 Use wildcards
 Use the IS NULL and IS NOT NULL keywords
 Use the ORDER BY clause
 Use the TOP keyword
 Use the DISTINCT keyword
 Use aggregate functions in queries
 Group result sets
 Use the COMPUTE and COMPUTE BY clause
 Use functions
 Use different types of joins
 Use subqueries
 Use subqueries with the IN clause
 Use subqueries with the EXISTS clause
 Use nested subqueries
 Use correlated subqueries
 Use the SELECT INTO statement
 Use the UNION operator
 Create tables
 Insert rows into tables
 Delete tables
 Create a user-defined datatype
 List various types of data integrity
 Add the following constraints to tables:
o PRIMARY KEY
o UNIQUE
o FOREIGN KEY
o CHECK
o DEFAULT
 Create rules
 Create defaults
 Maintain data in a table by using
o INSERT statement
o UPDATE statement
o DELETE statement
 Truncate a table

Implementing Business Logic (24 hrs – 3 cycles)

 Create a clustered index
 Create a nonclustered index
 Use the Index Tuning Wizard
 Understand Index Enhancements
 Create, alter, drop, and rename views
 Update data using views
 Declare variables
 Print messages
 Use comments
 Use conditional statements
 Use the iteration statements
 Create a stored procedure
 Execute a stored procedure
 Pass parameters to a stored procedure
 Modify a stored procedure
 Return values from a stored procedure
 Return multiple values from a stored procedure
 Call a procedure from another stored procedure
 Recompile a stored procedure automatically
 Drop a stored procedure
 Create the INSERT, DELETE, and UPDATE triggers
 Modify triggers
 Drop triggers
 Enforce data integrity through triggers
 Use the AFTER and the INSTEAD OF triggers
 Declare cursors
 Open cursors
 Fetch data from cursors
 Close cursors
 Create user-defined functions
 Create a transaction
 Commit a transaction
 Rollback a transaction
 Rollback part of a transaction
 Use locks
 Understand deadlock

Administering a Database (16 hrs – 2 cycles)

 Identify system databases and tables
 Identify files and filegroups
 Create, modify, view, rename, and delete databases
 Identify database-level options
 Register a server
 Configure the remote servers and set the user connections
 Add and delete messages
 Create databases and tables using Enterprise Manager
 Identify the various components of replication models
 Identify how replication works
 Understand the replication models
 Perform bulk copy using the BCP utility.
 Perform data transfer using the DTS wizard
 SQL Server Security Features
 Creating User Logins
 Types of User Roles
 Types of Security Permissions
 Assigning Permissions

Implementing XML in SQL Server 2000 (8 hrs – 1 cycle)

 Identify the need for XML
 Create an XML document
 Work with an XML schema
 Create URL Queries
 Create XPath Queries