I'm going to throw the following at some sql gurus, please critique.
Thanks.
Snapshot of MS SQL Server (2000/2005) oriented SQL skills
-- use environment: US company (hence, var vs. nvar etc.)
-- start from core concepts
-- DDL
/* new case */
-- company sells products, so, we need a product table
/* Business Rules:
a) product name no longer than 60 characters across board;
b) product description no longer than 3000 characters, not mandatory;
c) Company has no more than 255 products
*/
create table magicProduct (productID tinyint primary key, productName
varchar(60) not null, productDesc varchar(3000), active bit default
(1));
/* BRs ... getting a bit sloppy due to time constraint*/
-- company must have customers, so, we create one here
create table customers (customerID int primary key, customerName
varchar(100), primaryContact varchar(45), address varchar(150), city
varchar(45), state char(2), zipCode char(5), phone varchar(12), email
varchar(55), monthYear_of_FirstDeal char(6), current bit default(1)).
-- products purchased by customers
create table productSoldToCustomers ((productID, customerID) primary
key, soldDate datetime, soldBy varchar(30), amount money)
/* existing environment */
-- DML etc to check and improve performance, data integrity
dbcc -- a starting point ... read it again
what else that comes with BOL along this line?
-- get a copy of business functions, what company ABC does, where it
wants to go etc.
-- get a copy of ERD
-- read / understand, to see/analyse how well the ERD reflect
company
goals/objectives. Also, room/flexibility for growth by the current
design
-- nuts and bolts of DML
-- query optimization techniques such as indexing, using Query
Analyzer's execution plan, what else?
store procedures, triggers, variable table, cursor
what else fancy goodies out there? Have something critical been
missed?
|
