CSE 640: Database Design & Development

1. CSE 640: Database Design & Development Asis: Ayça Sanin E-mail: a.sanin@iku.edu.tr

2. Lecture Objectives • Get started with SQL Server 2005 • Create a SQL database • Retrieve and Manipulate data • Use the transact SQL-programming language

3. Microsoft SQL Server 2005 • A Client/Server database management system • Consists of two components • A front-end component (The Client), which is used to present and manipulate data • A back-end component (The Server), whinch is used to store, retrieve, and protect databases.

4. Your SQL database and the applications you use with that database, typically takes one of two forms: • An Online Transaction Processing system in which users continually makes changes to the data in the database. • An Online Analytical Processing system, in which you primarily focus on analyzing the data in the database.

5. Microsoft SQL Server 2005 – a relational database management system (RDBMS) • An RDBMS uses established relationships between the data in a database to ensure the integrity of the data. • These relationships enable yout to prevent users from entering incorrect data.

6. Installing SQL Server 2005

16. Using SQL Server Management Studio

23. Configuring SQL Server to Accept Remote Connections

24. 1. Activating TCP/IP

25. Using the sqlcmd Utility(SQL Server Express) • You can manage your Microsoft SQL Server 2005 Express Edition (SQL Server Express) databases by using the Sqlcmd utility from the command prompt. To access the Sqlcmd utility and connect to a default instance of SQL Server, open command prompt, and type sqlcmd. • Sqlcmd • – [-U login id] [-P password] [-S server] [-H hostname] • –[ E t t d ti ] [ d d t b ] [ l liti t] – [-E trusted connection] [-d use database name] [-l login timeout] • – [-t query timeout] [-h headers] [-s colseparator] [-w screen width] • – [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] • – [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] • – [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [ Qqy][ ][ y] • – [-W remove trailing spaces] [-u unicode output] • – [-r[0|1] msgs to stderr] [-iinputfile] [-o outputfile] • – [-f <codepage> | i:<codepage>[,o:<codepage>]] • – [-k[1|2] remove[replace] control characters] • – [-y variable length type display width] [ y variable length type display width] • – [-Y fixed length type display width] • – [-p[1] print statistics[colon format]] • – [-R use client regional setting] [-b On error batch abort] • – [-v var = "value"...] • [ X[1] dibl d [ d it ithi ]] – [-X[1] disable commands[and exit with warning]] • – [-? show syntax summary]

26. 2. Expressing the port numberA. If we don’t run SQL Browser Service • From the command prompt • Sqlcmd –E –S ServerName,PortNumber

27. 2. Expressing the port numberB. If we are runing SQL Browser Service

28. 3.Firewall • Configuring the Windows Firewall • Add sqlservr.exe • Add also sqlbrowser.exe (If you are using SQL Browser Service)

29. SQL Server Browser Service • This service runs like a Windows service • How SQL Server Browser Works • If TCP/IP or VIA protocols are enabled, then the server is assigned a TCP/IP port.If named pipes protocol is enabled, then SQL Server listens on a specific named pipe.

30. Database Objects • The database itself • The transaction log • Tables • Filegroups • Diagrams • Views • Stored procedures • User-defined functions • Indexes • CLR assemblies • Reports • Full-text catalogs • User-defined data types • Roles • Users

31. When SQL Server is loaded, it starts with four system databases. • master • model • msdb • tempd

32. System Databases • “master” Database • Records all the system-level information for an instance of SQL Server. • “model” Databse • Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward. • “msdb” Databse • Is used by SQL Server Agent for scheduling alerts and jobs. • “tempdb” Databse • Is a workspace for holding temporary objects or intermediate result sets.

33. File Groups • Primary data files • Contains the startup information for the database and • Points to the other files in the database • User data and objects can be stored in this file • Every database has only one primary data file. • File name extension for primary data files is “.mdf”. • Secondary data files • These files are optional, user-defined, and store user data. • Can be used to spread data across multiple disks • If a database exceeds the maximum size, you can use secondary data files so the database can continue to grow. • File name extension for secondary data files is “.ndf”. • Transaction Log • Holds the log information that is used to recover the database. • There must be at least one log file for each database. • File name extension for transaction logs is “.ldf”.

34. Some Database Objects • Table • A table can be thought of as equating to an accountant’s ledger or an Excel spreadsheet. • Made up of what is called domain data (columns) and entity data (rows). • The actual data for the database isstored in the tables. • Contains the metadata (descriptive information about data) • Indexes • Exist only within the framework of a particular table or view. • Works much like the index does in the back of an encyclopedia. • provides us ways of speeding the lookup of our information. Indexes fall into two categories: • Clustered • Non-clustered • Triggers • Exist only within the framework of a table. • Are pieces of logicalcode that are automatically executed when certain things, such as inserts, updates, or deletes, happento your table. • Constrains • Exist only within the confines of a table. • Confine the data in your table to meet certain conditions.

35. Some Database Objects • Schemas • Provide an intermediate namespace between your database and the other objects it contains. • Default namespace in any database is “dbo” (which stands for database owner). • If the object is within a namespace that is not the default for that user, then the object must bereferred with two parts in the form of <schema name>.<object name>. • Diagrams • Are visual representations of the database design, including the varioustables, the column names in each table, and the relationships between tables. • Views • Are used just like a table, except that itdoesn’t contain any data of its own. • Are merely preplanned mapping and representationof the data stored in tables. • The plan is stored in the database in the form of a query, which calls data from necessary columns of tables.

36. Some Database Objects • Stored Procedures • Are generally an ordered series ofTransact-SQL (the language used to query Microsoft SQL Server) statements bundled up into a singlelogical unit. • Allow for variables and parameters as well as selection and looping constructs. • User-Defined Functions • Can return a value of most SQL Server data types. Excluded return types include text, ntext,image, cursor, and timestamp. • Are similar to the functions that you would use in a standard programming languages. • User-Defined Data Types • User-defined data types are extensions to the system-defined data types. Beginning with this version ofSQL Server, the possibilities here are almost endless. Although SQL Server 2000 and earlier had the ideaof user-defined data types, they were really limited to different filtering of existing data types. With SQLServer 2005, you have the ability to bind .NET assemblies to your own data types, meaning you canhave a data type that stores (within reason) about anything you can store in a .NET object. • Recognize that it will almost certainly come with a large performance cost. Consider it carefully.

37. Some Database Objects • Users and Roles • These two go hand in hand. Users are pretty much the equivalent of logins. In short, this object represents an identifier for someone to log in to the SQL Server. Anyone logging into SQL Server has to map(directly or indirectly depending on the security model in use) to a user. Users, in turn, belong to one ormore roles. Rights to perform certain actions in SQL Server can then be granted directly to a user or to arole to which one or more users belong. • Rules • Rules and constraints provide restriction information about what can go into a table. If an updated orinserted record violates a rule, then that insertion or update will be rejected. In addition, a rule can beused to define a restriction on a user-defined data type. Unlike rules, constraints aren’t really objects untothemselves but rather pieces of metadata describing a particular table. • Defaults • When inserting a record, if you don’t provide the value of a column and that column has a defaultdefined, a value will be inserted automatically as defined in the default.

38. Some Database Objects • Full-Text Catalogs • Are mappings of data that speed the search for specific blocks of text within columnsthat have full-text searching enabled. • Although these objects are joined at the hip to the tables andcolumns that they map, they are separate objects and are as such, not automatically updated whenchanges happen in the database.