Posted by planetcall Wed 21st Mar 2007 21:34 - Syntax is SQL - 47 views
Download | New Post | Modify | Hide line numbers
Description:
Database design for my project

  1.  
  2. USE master
  3. IF EXISTS (SELECT * FROM sysdatabases WHERE name='MyShop')
  4. begin
  5. DROP DATABASE MyShop
  6. Print 'Previous database MyShop dropped'
  7. end
  8. CREATE DATABASE MyShop
  9. Print 'Database MyShop created'
  10. USE MyShop
  11. Print 'Using database MyShop'
  12. go
  13.  
  14. CREATE TABLE EmpType(
  15. EType int PRIMARY KEY,
  16. ETypeName varchar(15),
  17. --ETypeLevel int --sets the level of a Employee Type. It is for access permissions
  18. )
  19. print 'Table Created: EmpType'
  20. INSERT INTO EmpType VALUES(001,'Administrator')
  21. INSERT INTO EmpType VALUES(002,'StockManager')
  22. INSERT INTO EmpType VALUES(003,'Cashier')
  23. INSERT INTO EmpType VALUES(004,'Customer')
  24.  
  25. CREATE TABLE Employee (
  26.     EID int PRIMARY KEY,
  27.     EName varchar(15) NOT NULL,
  28.     EType int REFERENCES EmpType(EType),
  29.     EAddress varchar(30) NOT NULL,
  30.     EDOJ datetime NOT NULL
  31. )
  32. print 'Table Created: Employee'
  33. INSERT INTO Employee VALUES(001,'Abhishek',001,'ShantiNagar,Bangalore',2/2/2006)
  34. INSERT INTO Employee VALUES(002,'Anthony',002,'JPNagar 4th phase',2/3/2006)
  35. INSERT INTO Employee VALUES(003,'Johny',003,'WilsonGarden',5/5/2005)
  36. INSERT INTO Employee VALUES(004,'Paul',004,'VidhanSaudha',9/2/2007)
  37.  
  38. CREATE TABLE EmpLogin(
  39. EID int PRIMARY KEY REFERENCES Employee(EID),
  40. EPass char(8) NOT NULL
  41. )
  42. print 'Table Created: EmpLogin'
  43. INSERT INTO EmpLogin VALUES(001,'1234')
  44. INSERT INTO EmpLogin VALUES(002,'123123')
  45.  
  46. CREATE TABLE ProdType(
  47.     PType int PRIMARY KEY,
  48.     PTypeName varchar(15)
  49. )
  50. print 'Table Created: ProdType'
  51.  
  52. CREATE TABLE Product(
  53.     PID int PRIMARY KEY,
  54.     PName char(20) UNIQUE NOT NULL,
  55.     PType int REFERENCES ProdType(PType)
  56. )
  57. print 'Table Created: Product'
  58.  
  59. CREATE TABLE ProdPrice(
  60.     PID int PRIMARY KEY REFERENCES Product(PID),
  61.     PMRP money NOT NULL, --Maximum Retail Price
  62.     PDiscount money --Discount on the product in Rupee;
  63. )
  64. print 'Table Created: ProdPrice'
  65.  
  66. CREATE TABLE Store(
  67.     PID int PRIMARY KEY REFERENCES Product(PID),
  68.     PCount int --depletes with every item sold and could be updated as desired
  69. )
  70. print 'Table Created: Store'
  71.  
  72. CREATE TABLE LogCashier( -- Creates a log of the cashier's performance
  73.     ReceiptID int PRIMARY KEY, -- the unique number of the receipt ex. Date(6chars)-Time(6chars)-2rands.(ex. 210307094398)
  74.     EID int REFERENCES Employee(EID),
  75.     LogDate datetime, --contains the time and date of the specific log entry
  76.     Value money --stores the monetary value of the transaction
  77. )
  78. print 'Table Created: LogCashier'
  79. go
  80.  
  81. CREATE VIEW V_Product AS
  82. SELECT P.PID , P.PName, PP.PMRP, PP.PDiscount , PP.PMRP-PP.PDiscount AS FinalPrice FROM Product P, ProdPrice PP
  83. go
  84. print 'View Created: V_Product'
  85. go
  86. IF EXISTS(SELECT name FROM sysobjects WHERE name = 'ProdCountUpdate' AND type = 'P')
  87. begin
  88. DROP proc ProdCountUpdate
  89. print 'Dropped: Stored Procedure ProdCountUpdate'
  90. end
  91. go
  92.  
  93. CREATE proc ProdCountUpdate --Product.PID,Count
  94. @pid int,
  95. @quantity int
  96. AS
  97. UPDATE store SET PCount = (PCount-@quantity) WHERE PID = @pid
  98.  
  99. print 'Stored Procedure Created: ProdCountUpdate( pid, quantity)'
  100. go
  101.  
  102.  --reference to datetime format conversion http://www.databasejournal.com/features/mssql/article.php/2197931

PermaLink to this entry https://pastebin.co.uk/12129
Posted by planetcall Wed 21st Mar 2007 21:34 - Syntax is SQL - 47 views
Download | New Post | Modify | Hide line numbers

 

Comments: 0