Information Technologies and Microsoft SQL Server

1. Information Technologies and Microsoft SQL Server Day 1 by Alper Özpınar ozpinar@zetabt.com

2. Course Overview • Introduction to Information Technologies • Historical background • Data and information • Data collecting and storing • Data processing • SQL Server • SQL Server Technical Details • Creating a database • Security and users • Table’s and data types • SQL Language • SQL Functions • Stored Procedures • Applications

3. Structure of World Economy

4. Computers and Humanbeings "I think there is a world market for maybe five computers." Thomas Watson (1874-1956), Chairman of IBM, 1943

5. Computers and Humanbeings "There is no reason anyone would want a computer in their home." Ken Olson, president, chairman and founder of Digital Equipment Corp., 1977

6. Structureof old economy The capital rise and grow • Most of the producers have a local or national market competition, not more than a competition with neighboring countries • Competition based on costing and quality • Product improvements still continues while the product on the market • Products have a long market life • Product development includes continues actions

7. Structure of new economy The information rise and grow • Competition • Competition in world markets • Management and control in a global marketplace • Global work groups & delivery systems • Products & Services • Complex and variable • Short market life • Product & Service development processes are mostly parallel and multidisciplinary

8. Structure of new economy • Weapons of the competitive market • Perfection in product & service design • Creativity and Innovation • Flexible to the customer demands • High quality • New product development and entering the market timing • Limited employee knowledge base • Leadership

9. Structure of new economy • Production & Services • Ready to work with uncertainty and fuzzy situations • Flexible network production • Distributed & Outsourced • All disciplines and departments works parallel in production • Low capacity high flexibility • Cheaper • Faster • Durable • Reliable

10. Structure of new economy • Transformation of the Enterprise • Flattening • Decentralization • Flexibility • Location Independence • Low transaction and coordination costs • Empowerment • Collaborativework and teamwork

11. Structure of new economy • New way of operating the business • SCM (Supply Chain Management ) • MRP ( Management Resources Planning) • MIS ( Management Information Systems ) • ERP (Enterprise Resource Planning ) • CRM (Customer Relationship Management ) • ...

12. Functions of Information Systems Environment Customers Suppliers Organization Input Output Processing Feedback Competitors Regulatory Agencies Stockholders

13. Information Systems Information Systems

14. Key Systems in Organization Because there are different interests, specialities, and levels in an organization, there are different types of systems in an organization these are; • Operational-level Systems • Knowledge-level Systems • Management-level Systems • Strategic-level Systems

15. Time Sequence • mid-1950s Transaction Processing Systems (TPS) • 1960s MIS • 1970s Office Automation Systems DSS • 1980s DSS Expanded Commercial applications of expert systems Executive Information Systems • 1990s Group Support Systems Neural Computing Integrated, hybrid computer systems

16. KIND OF SYSTEM GROUPS SERVED STRATEGIC LEVEL SENIOR MANAGERS MANAGEMENT LEVEL MIDDLE MANAGERS KNOWLEDGE LEVEL KNOWLEDGE & DATA WORKERS OPERATIONAL OPERATIONAL LEVEL MANAGERS SALES & MANUFACTURING FINANCE ACCOUNTING HUMAN MARKETING RESOURCES TYPES OF INFORMATION SYSTEMS

17. Operational-level Systems Support operational managers by keeping track of the elementary activities and transactions of the organisation. The principle purpose of systems at this level is to answer routine questions and track the flow of transactions through the organisation. Covers things such as sales, receipts, cash deposits, payroll, credit decisions, flow of materials.

18. Knowledge-level Systems Support knowledge and data workers in an organisation. The purpose of these systems is to help the organisation discover, organise and integrate new and existing knowledge in to the business, and to help control the flow of paperwork. These systems, specially in the form of collaboration tools, workstations, and office systems, are the fastest growing applications in business today.

19. Management-level Systems Designed to serve the the monitoring, controlling, decision-making, and administrative activities of middle managers. These typically provide periodic reports rather than instant information on operations. Some of these systems support non-routine decision-making, focusing on less-structured decisions for which information requirements are not always clear. This will often require information from outwith the organisation, as well as from normal operational-level data.

20. Strategic-level Systems Help senior management tackle and address strategic issues and long-term trends, both within the organisation and in the external environment. Principal concern is matching organisational capability to changes, and opportunities, occurring in the medium to long term (i.e. 5 - 10 years) in the external environment.

21. Systems Typically, an organisation might have operational, knowledge, management and strategic level systems for each functional area within the organisation. This would be based on the management model adopted by the organisation, so, while the most commonly-adopted systems structure would simply follow the standard functional model, structures reflecting bureaucratic, product and matrix models are also possible.

24. TPS MIS SALES DATA Order Processing System UNIT PRODUCT COST ORDER FILE Materials Resource Planning System MIS REPORTS PRODUCT CHANGE DATA PRODUCTION MASTER FILE General Ledger System EXPENSE DATA MANAGERS ACCOUNTING FILES MIS FILES TPS DATA FOR MIS APPLICATIONS

25. Decision Support Systems Repetitive Linear Logic Regular Reports No support of decisions Specialised heuristics System makes decision itself No regular reports TPS OAS MIS ESS/EIS DSS KWS ES

26. Decision-Support Systems (DSS) Decision Maker Components of a DSS MMS Management Support Systems DSS User Interface DBMS Database Management Systems MBMS Model Base Management Services Mail, News, Discussion Groups Corporate Databases Models and Aids

27. ESS MIS DSS KWS OAS TPS Relations

28. "There are two ways of constructing a software design; one way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult." - C. A. R. Hoare

30. What is a Database? • The term database has fallen into loose use lately, losing much of its original meaning. To some people, a database is any collection of data items (phone books, laundry lists, parchment scrolls . . . whatever). • A record is a representation of some physical or conceptual object. Say, for example, that you want to keep track of a business’s customers. You assign a record for each customer. Each record has multiple attributes, such as name,address, and telephone number. Individual names, addresses, and so on are the data.

31. What Is a Database ManagementSystem? • A database management system (DBMS) is a set of programsused to define, administer, and process databases and their associated applications.The database being “managed” is, in essence, a structure that youbuild to hold valuable data. • A DBMS is the tool you use to build that structureand operate on the data contained within the database. • Many DBMS programs are on the market today. Some run only on mainframecomputers, some only on minicomputers, and some only on personal computers.

32. What is SQL Server 2000? • SQL Server is a client/server based relational database management system • Runs on Windows 2000 Professional, Server, Advanced Server, NT 4, Windows 9x/ME or Windows CE • Included in BackOffice product family • Include in .Net Servers family

33. Server Side Database Engine Security Fault-tolerance Performance Concurrency Reliable backup Client Side User Interface Forms Reports Queries Client/Server

34. Desktop Databases

35. Client/Server Database

36. Types of Databases • Relational • Flat-File • Hierarchical • Relational Vs. Flat-file

37. Database Objects • Table • Row • Column • Data Type • Stored Procedure • Trigger • Rule • Default • View • Index

38. Data-Warehousing Decision Support Systems (DSS) Online Analytical Processing (OLAP) Relatively fixed data Long running queries Online Transaction Processing (OLTP) Continuously changing data Updates relatively small transactions Data-Warehousing Vs. Transaction Processing SQL Server works well with either applications

39. Background on SQL Server & SQL • History • Position in the Market • History of SQL – IBM 1970 • SQL and SEQUEL • ANSI-SQL, T-SQL / PL-SQL SQL Language • DDL Data Definition Language • DML Data Manipulation Language Brief History of Windows and Versions of Windows 2000

40. SQL Server Product Roadmap SQL Server 6.5 SQL Server 7.0 SQL Server 2000 SQL Server “Yukon” • Data warehousing • Internet support • Differentiation from Sybase SQL Server • Re-architecture of relational server • First to include OLAP in database • Auto tuning • Ease-of-use • Reliability and scalability advancements • Deep XML support • Data warehousing • SQL Server CE • 64 bit support • Enterprise-class scalability • Programmability advancements • End-to-end business intelligence • Manageability • Support for multiple types of data • Lowest TCO • Ease-of-Use • Performance and Scalability • Integrated Business Intelligence Foundations of each release:

41. Versions of SQL Server 2000 • SQL Server CE • Runs on Windows CE • Replicate data from Standard and Enterprise Edition • Personal • Runs on Windows 9x/ME/2000 Pro • No license required if you have Standard or Enterprise Edition • Developer • Designed for Developers to be used on Single Machine • Runs on Windows NT/2000 • Standard • Comes with most of the features for workgroups and departments • Lacks dome enterprise level features • Runs on Windows NT/2000 Server • Enterprise: • All features including clustering support, log shipping, parallel computing support, enhanced read-aheads, partitioning support, HTTP support, Very Large Database (VLDB) Support • Runs on Windows NT/2000 Server

43. SQL Server 2000 Clients • Directly Supported: • Windows 9x/ME • Windows 2000 • Windows NT • Does not ship with 16-bit drivers, however you can use drivers from old version of SQL Server • When correctly configured with Web Server, any client can access it

44. Differences on Windows 9x

45. Special System Databases • A new SQL Server 2000 installation automatically includes six databases: master, model, tempdb, pubs, Northwind, and msdb.

46. master • The master database is composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations, usage, systemwide configuration settings, login accounts, the existence of other databases, and the existence of other SQL servers (for distributed operations). The master database is absolutely critical to your system, so be sure to always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such activities, you should back up master.

47. model • The model database is simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database. If you'd like every new database to start out with certain objects or permissions, you can put them in model, and all new databases will inherit them.

48. Pubs & Northwind • The pubs database is a sample database used extensively by much of the SQL Server documentation • The Northwind database is a sample database that was originally developed for use with Microsoft Access. Much of the documentation dealing with APIs uses Northwind, as do some of the newer examples in the SQL Server documentation. It's a bit more complex than pubs, and at almost 4 MB, slightly larger. • The Northwind database can be rebuilt just like the pubs database, by running a script located in the \Install subdirectory. The file is called Instnwnd.sql.

49. msdb • The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks. In general, other than performing backups and maintenance on this database, you should ignore msdb.

50. Database Files • A database file is nothing more than an operating system file. (In addition to database files, SQL Server also has backup devices, which are logical devices that map to operating system files, to physical devices such as tape drives, or even to named pipes. : • Primary data files Every database has one primary data file that keeps track of all the rest of the files in the database, in addition to storing data. By convention, the name of a primary data file has the extension MDF. • Secondary data files A database can have zero or more secondary data files. By convention, the name of a secondary data file has the extension NDF. • Log files Every database has at least one log file that contains the information necessary to recover all transactions in a database. By convention, a log file has the extension LDF.