Downloaded 35 times
![298
]](https://image.slidesharecdn.com/sybasease15-140815161742-phpapp01/85/DB-3-Sybase-ASE-15-MS-SQL-Server-299-320.jpg)
![298
]](https://image.slidesharecdn.com/sybasease15-140815161742-phpapp01/75/DB-3-Sybase-ASE-15-MS-SQL-Server-299-2048.jpg)
Uploaded bySunny U Okoro
DB 3 Sybase ASE 15 & MS SQL Server
This document describes the creation and testing of databases using various database management systems including Sybase ASE 15 and 16, Microsoft SQL Server 2012 and 2008 R2, and Microsoft Access 2010. It covers topics such as database creation, table design, indexing, triggers, population, verification, and manipulation of data. For each database system, details are provided about setting up the database structure, populating tables with data, running tests and queries, and addressing any issues that came up.
More Related Content
Similar to DB 3 Sybase ASE 15 & MS SQL Server
More from Sunny U Okoro
DB 3 Sybase ASE 15 & MS SQL Server
- 1. Sybase ASE 16,15, Access 2010 & MS SQL Server 2012 & 2008R2 By Sunny Okoro
- 2. 1 Contents DatabaseSystems...............................................................................................................................4 Application.........................................................................................................................................5 Sybase ASE ....................................................................................................................................... 10 ASE 15.......................................................................................................................................... 10 ASE 16.......................................................................................................................................... 17 Database Creation ..................................................................................................................... 19 Tables Creation ......................................................................................................................... 19 Database Diagram ..................................................................................................................... 24 Audit Tables Creation ................................................................................................................ 24 Triggers .................................................................................................................................... 36 Indexes ..................................................................................................................................... 43 Database Dictionary .................................................................................................................. 46 Table Population ...................................................................................................................... 58 Revised Physical Database Diagram ............................................................................................ 79 Data Verfication ........................................................................................................................ 90 Data Manipulation .................................................................................................................. 111 Microsoft SQL Server 2012 .............................................................................................................. 138 Database Creation ...................................................................................................................... 139 Tables and Index ......................................................................................................................... 143 Triggers ...................................................................................................................................... 150 Database Dictionary .................................................................................................................... 154 Database Physical Diagram .......................................................................................................... 171 Table Population and Verification ................................................................................................ 172 DBA ........................................................................................................................................... 177 Data Manipulation ...................................................................................................................... 179 Xml ............................................................................................................................................ 248 Microsoft SQL Server 2008 .............................................................................................................. 261 Database Creation ...................................................................................................................... 263 Tables and Indexes...................................................................................................................... 265 Triggers ...................................................................................................................................... 270 Audit Records ............................................................................................................................. 273
- 3. 2 Database Dictionary.................................................................................................................... 275 Database Physical Diagram .......................................................................................................... 286 ETL ............................................................................................................................................. 288 Data Verification ......................................................................................................................... 292 ETL 2 .......................................................................................................................................... 302 Data Manipulation ...................................................................................................................... 307 Microsoft Access 2010 .................................................................................................................... 317 Linked Server .............................................................................................................................. 317 Imported Data ............................................................................................................................ 329 Database Creation ...................................................................................................................... 336 ETL ......................................................................................................................................... 343 Microsoft Excel............................................................................................................................... 357 Linked Server .............................................................................................................................. 357
- 4.
- 5. 4 Database Systems Sybase Adaptive Server Microsoft SQL Server 2012
- 6. 5 Microsoft SQLServer 2008R2 Microsoft Access 2010 Oracle 11Gr2 Application
- 7. 6 Oracle SQLDeveloper Sybase Central Dell Quest TOAD SQL Server Edition
- 8. 7 Microsoft VisualStudio 2008 Microsoft SQL Server Management Studio
- 9. 8 SAP InteractiveSQL Microsoft SSIS 2012/ SQL Server Data Tools
- 10. 9 Microsoft Visio2010 Oracle JDeveloper 12c
- 11. 10 Talend OpenStudio Sybase ASE ASE 15
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19. 18 Store PKStore_ID Store_Name Address City State Phone HRRecords PK,FK1 Employee_ID PK,FK2 Store_ID PK HR_ID Employee Hire_Date Hire_EndDate DOB PK Employee_ID First_Name Midd_Name Last_Name Address City State Phone Customer PK Customer_ID PK,FK1 Store_ID First_Name Midd_Name Last_Name Address City Phone Orders PK Order_ID PK,FK3 Product_ID PK,FK1 Store_ID PK,FK2 Employee_ID QTY Order_Notes Order_Date Order_Amt Order_Tax Order_FinalAmt Order_Ship Shipment PK Shipping_ID PK,FK1 Order_ID PK,FK1,FK2 Employee_ID Shipping_Date Shipping_Amt Recieve_Date ProductCategory PK Category_ID Category_Name Description Product PK Product_ID PK,FK1 Category_ID Porudct_Name Product_Flavor Product_BoxQTY
- 20. 19 Database Creation Tables Creation
- 21.
- 22.
- 23.
- 24.
- 25. 24 Database Diagram Audit Tables Creation
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68. 67 This storedprocedure will be utilized for table population.
- 69.
- 70.
- 71.
- 72. 71 Due toan error with the table and stored procedure utilized for the Order table population, the entire triggers and tables associated with Orders and Shipment tables were dropped and recreated.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80. 79 Revised PhysicalDatabase Diagram
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115. 114 Results Abridged Results Abridged
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139. 138 Microsoft SQLServer 2012
- 140.
- 141.
- 142.
- 143.
- 144. 143 Tables andIndex
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172. 171 Database PhysicalDiagram Customer PK,I2,I1 Customer_ID INTEGER PK,FK1,I1 Store_ID INTEGER U2 First_Name CHAR(45) Mid_Name CHAR(45) U3 Last_Name CHAR(45) Address CHAR(55) U1 City CHAR(40) Phone CHAR(35) U4 Customer_State CHAR(8) Product PK,I2,I1 Product_ID INTEGER PK,FK1,I1 Category_ID INTEGER Product_Name CHAR(50) Product_BoxQTy INTEGER Employee PK,I1 Employee_ID INTEGER U2 First_Name CHAR(40) Midd_Name CHAR(40) U3 Last_Name CHAR(40) Adress CHAR(55) U1 City CHAR(40) U4 State CHAR(5) Phone CHAR(25) ProductCategory PK,I1 Category_ID INTEGER Category_Name CHAR(50) Description CHAR(32767) PK,FK1,I1 Employee_ID INTEGER PK,FK2,I1 Store_ID INTEGER PK,I2,I1 HR_ID INTEGER U2 Hire_Date WCHAR(10) U1 DOB WCHAR(10) shipment HRRecords PK,I2,I1 Shipping_ID INTEGER PK,FK2,I1 Order_ID INTEGER PK,FK1,I1 Employee_ID INTEGER U2 Shipping_Date WCHAR(10) U1 Shipping_Amt CURRENCY Recieve_Date WCHAR(10) U3 Shipping_Type CHAR(35) Store PK,I1 Store_ID INTEGER U3 Store_Name CHAR(35) Address CHAR(45) U1 City CHAR(40) U2 State CHAR(5) Phone CHAR(25) orders PK,I2,I1 Order_ID INTEGER PK,FK3,I1 Product_ID INTEGER PK,FK4,I1 Store_ID INTEGER PK,FK1,I1 Customer_ID INTEGER PK,FK2,I1 Employee_ID INTEGER Order_QTY INTEGER Order_Notes CHAR(200) U3 Order_Date WCHAR(10) U2 Order_Amt CURRENCY Order_Tax CURRENCY U1 Order_FinalAmt CURRENCY Order_Ship CHAR(9)
- 173. 172 Table Populationand Verification
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
- 208.
- 209.
- 210.
- 211.
- 212.
- 213.
- 214.
- 215.
- 216.
- 217.
- 218.
- 219.
- 220.
- 221.
- 222.
- 223.
- 224.
- 225.
- 226.
- 227.
- 228.
- 229.
- 230.
- 231.
- 232.
- 233.
- 234.
- 235.
- 236.
- 237.
- 238.
- 239.
- 240.
- 241.
- 242.
- 243.
- 244.
- 245.
- 246.
- 247.
- 248.
- 249.
- 250.
- 251.
- 252.
- 253.
- 254.
- 255.
- 256.
- 257.
- 258.
- 259.
- 260.
- 261.
- 262. 261 Microsoft SQLServer 2008
- 263.
- 264.
- 265.
- 266. 265 Tables andIndexes
- 267.
- 268.
- 269.
- 270.
- 271.
- 272.
- 273.
- 274.
- 275.
- 276.
- 277.
- 278.
- 279.
- 280.
- 281.
- 282.
- 283.
- 284.
- 285.
- 286.
- 287. 286 Database PhysicalDiagram
- 288. 287 Customer PK,I2,I1Customer_ID PK,I1 Store_ID U2 First_Name Mid_Name U3 Last_Name Address U1 City Phone U4 Customer_State Product PK,I2,I1 Product_ID PK,FK1,I1 Category_ID U1 Product_Name Product_BoxQTy Employee PK,I1 Employee_ID U2 First_Name Midd_Name U3 Last_Name Adress U1 City U4 State Phone ProductCategory PK,I1 Category_ID Category_Name Description shipment PK,I2,I1 Shipping_ID PK,FK2,I1 Order_ID PK,FK1,I1 Employee_ID U2 Shipping_Date U1 Shipping_Amt Recieve_Date U3 Shipping_Type HRRecords PK,FK1,I1 Employee_ID PK,FK2,I1 Store_ID PK,I2,I1 HR_ID U2 Hire_Date U1 DOB Store PK,I1 Store_ID U3 Store_Name Address U1 City U2 State Phone orders PK,I2,I1 Order_ID PK,FK3,I1 Product_ID PK,FK4,I1 Store_ID PK,FK1,I1 Customer_ID PK,FK2,I1 Employee_ID Order_QTY Order_Notes U3 Order_Date U2 Order_Amt Order_Tax U1 Order_FinalAmt Order_Ship
- 289.
- 290.
- 291.
- 292.
- 293.
- 294.
- 295.
- 296.
- 297.
- 298.
- 299.
- 300.
- 301.
- 302.
- 303. 302 ETL 2 The underlying audit tables remained empty after SSIS has been utilized to populate the database which did not activate the audit triggers. The data in all the populated tables will be deleted, the identity column option would be set back to on and Talend would be utilized to populate the store table. SSIS would be utilized to repopulate the rest of the tables.
- 304.
- 305.
- 306.
- 307.
- 308.
- 309.
- 310.
- 311.
- 312.
- 313.
- 314.
- 315.
- 316.
- 317.
- 318. 317 Microsoft Access2010 Linked Server
- 319.
- 320.
- 321.
- 322.
- 323.
- 324.
- 325.
- 326.
- 327.
- 328. 327 Changed thefirst name to Philip for employee id 631 and re verify if the changes has been applied in SQL Server
- 329.
- 330.
- 331.
- 332.
- 333.
- 334.
- 335.
- 336. 335 Linked serverdoes not apply since the data was exported to MySQL Server
- 337. 336 Microsoft Access2010 Employee XML Sheet Database Creation
- 338.
- 339.
- 340.
- 341.
- 342.
- 343.
- 344. 343 ETL Oraclestudent sample database would be utilized to populate the course, section and instructor table created in Access.
- 345.
- 346.
- 347.
- 348.
- 349.
- 350.
- 351.
- 352.
- 353.
- 354.
- 355.
- 356. 355 Table hasbeen removed as a result the view failed to open. I have to change the query to point to courseclass2 table which has the same query definition from the old section table but was a table not a view.
- 357.
- 358. 357 Microsoft Excel Linked Server
- 359.
- 360.
- 361.
- 362.