- Количество слайдов: 53
Information Technologies and Microsoft SQL Server Day 1 by Alper Özpınar [email protected] com
Course Overview n Introduction to Information Technologies Historical background n Data and information n Data collecting and storing n Data processing n SQL Server Technical Details n Creating a database n Security and users n Table’s and data types n SQL Language n SQL Functions n Stored Procedures n Applications n
Structure of World Economy High Imperialism 1880 - 1914 The World War I between the Wars 1918/23 - 1914 - 1918/23 1939 World War II Post World War 1939 - 1945 II 1945 - 1949 Industrialization War Economy Political Issues Demand >>> Supply Demand >> Supply The Early Cold War 1949 - 1969 The Late Cold War 1969 - 1990 Technology Race New Business Structure Globalization Demand >> Supply Demand = Supply Demand <<< Supply New World 1990 -2000 Now
Computers and Humanbeings "I think there is a world market for maybe five computers. " Thomas Watson (1874 -1956), Chairman of IBM, 1943
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
Structure of old economy The capital rise and grow n Most of the producers have a local or national market n n 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
Structure of new economy The information rise and grow n Competition in world markets Management and control in a global marketplace n Global work groups & delivery systems n n Products & Services n n n Complex and variable Short market life Product & Service development processes are mostly parallel and multidisciplinary
Structure of new economy n Weapons of the competitive market n n n n 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
Structure of new economy n Production & Services n n n n n 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
Structure of new economy n Transformation of the Enterprise n n n n Flattening Decentralization Flexibility Location Independence Low transaction and coordination costs Empowerment Collaborativework and teamwork
Structure of new economy n 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 ). . .
Functions of Information Systems Environment Customers Suppliers Organization Input Processing Output Feedback Regulatory Agencies Competitors Stockholders
Information Systems Organizations Management Information Systems Technology
Key Systems in Organization 1. 2. 3. 4. 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
Time Sequence n mid-1950 s n 1960 s n 1970 s n 1980 s n 1990 s Transaction Processing Systems (TPS) MIS Office Automation Systems DSS Expanded Commercial applications of expert systems Executive Information Systems Group Support Systems Neural Computing Integrated, hybrid computer systems
TYPES OF INFORMATION SYSTEMS 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
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.
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.
Management-level Systems Designed to serve 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 decisionmaking, 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.
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.
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.
TPS DATA FOR MIS APPLICATIONS TPS Order Processing System ORDER FILE Materials Resource Planning System PRODUCTION MASTER FILE General Ledger System ACCOUNTING FILES MIS SALES DATA UNIT PRODUCT COST MIS REPORTS PRODUCT CHANGE DATA EXPENSE DATA MIS FILES MANAGERS
Decision Support Systems Repetitive Linear Logic Regular Reports No support of decisions TPS OAS Specialised heuristics System makes decision itself No regular reports MIS ESS/EIS DSS KWS ES
Decision-Support Systems (DSS) Components of a DSS Decision Maker MMS DSS User Interface Management Support Systems DBMS MBMS Database Management Systems Model Base Management Services Corporat e Databas es Models and Aids Mail, News, Discussion Groups
Relations ESS MIS KWS OAS DSS TPS
n "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
What is a Database? n 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). n 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.
What Is a Database Management System? n A database management system (DBMS) is a set of programs used to define, administer, and process databases and their associated applications. The database being “managed” is, in essence, a structure that you build to hold valuable data. n A DBMS is the tool you use to build that structure and operate on the data contained within the database. n Many DBMS programs are on the market today. Some run only on mainframe computers, some only on minicomputers, and some only on personal computers.
What is SQL Server 2000? n SQL Server is a client/server based relational database management system n Runs on Windows 2000 Professional, Server, Advanced Server, NT 4, Windows 9 x/ME or Windows CE n Included in Back. Office product family n Include in. Net Servers family
Client/Server Side n Database Engine n Security n Fault-tolerance n Performance n Concurrency n Reliable backup Client Side n User Interface n Forms n Reports n Queries
Types of Databases n Relational n Flat-File n Hierarchical n Relational Vs. Flat-file
Database Objects • • • Table Row Column Data Type Stored Procedure Trigger Rule Default View Index
Data-Warehousing Vs. Transaction Processing Data-Warehousing n Decision Support Systems (DSS) n Online Analytical Processing (OLAP) n Relatively fixed data n Long running queries Online Transaction Processing (OLTP) n Continuously changing data n Updates relatively small transactions SQL Server works well with either applications
Background on SQL Server & SQL History Position in the Market History of SQL – IBM 1970 SQL and SEQUEL ANSI-SQL, T-SQL / PL-SQL Language n DDL Data Definition Language n DML Data Manipulation Language Brief History of Windows and Versions of Windows 2000 n n n
SQL Server Product Roadmap SQL Server 6. 5 SQL Server 7. 0 • 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 Foundations of each release: • Lowest TCO • Ease-of-Use SQL Server 2000 • Reliability and scalability advancements • Deep XML support • Data warehousing • SQL Server CE • 64 bit support SQL Server “Yukon” • Enterprise-class scalability • Programmability advancements • End-to-end business intelligence • Manageability • Support for multiple types of data • Performance and Scalability • Integrated Business Intelligence
Versions of SQL Server 2000 n SQL Server CE n n n Personal n n Designed for Developers to be used on Single Machine Runs on Windows NT/2000 Standard n n Runs on Windows 9 x/ME/2000 Pro No license required if you have Standard or Enterprise Edition Developer n n Runs on Windows CE Replicate data from Standard and Enterprise Edition Comes with most of the features for workgroups and departments Lacks dome enterprise level features Runs on Windows NT/2000 Server Enterprise: n n 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
Feature Personal Standard Enterprise Runs on Microsoft Windows NT 4 Server or Windows 2000 Server Yes Yes Runs on Windows NT 4 Server, Enterprise Edition or Windows 2000 Advanced Server Yes Yes AWE Support (Windows 2000 only) No No Yes SQL Server failover support No No Yes Supports Microsoft Search Service, full-text catalogs, and full-text indexes Yes, except on Windows 98 Yes Maximum database size 2 GB 1, 048, 516 TB 4 on all platforms except Windows NT 4 Server, Enterprise Edition, which supports 8 32 on Windows 2000 Datacenter Server 8 on Windows NT 4 Server Enterprise Edition and Windows 2000 Advanced Server 4 on Windows NT 4 Server and Windows 2000 Server 2 GB 64 GB on Windows 2000 Datacenter Server 8 GB on Windows 2000 Advanced Server 4 GB on Windows 2000 Server 3 GB on Windows NT 4 Server, Enterprise Edition 2 GB on Windows NT 4 Server Number of symmetric multiprocessing (SMP) CPUs Physical memory supported 2 on all platforms except Windows 98, which supports only 1 2 GB
SQL Server 2000 Clients n Directly Supported: n Windows 9 x/ME n Windows 2000 n Windows NT n Does not ship with 16 -bit drivers, however you can use drivers from old version of SQL Server n When correctly configured with Web Server, any client can access it
Differences on Windows 9 x Windows 2000 AS SQL Engine Runs as an application Runs as a Service Integrated Security No Yes Automated Alerts No Yes Maximum Users (Recommended) Five Unlimited (Limited by Hardware resources) Performance Monitor No Yes
Special System Databases n A new SQL Server 2000 installation automatically includes six databases: master, model, tempdb, pubs, Northwind, and msdb.
master n 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.
model n 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.
Pubs & Northwind n The pubs database is a sample database used extensively by much of the SQL Server documentation n 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. n 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.
msdb n 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.
Database Files n 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. : n 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. n 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. n 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.
Creating a Database n The easiest way to create a database is to use SQL Server Enterprise Manager, which provides a graphical front end to Transact. SQL commands and stored procedures that actually create the database and set its properties n by Command n CREATE DATABASE newdb
Creating a Table
Creating a Table