1 nassau community collegeprof. vincent costa acknowledgements: introduction to database management,...

1Nassau Community CollegeProf. Vincent CostaAcknowledgements: Introduction to Database Management, All Rights Reserved

international, open membership, not-for-profit technology standards consortium.

Session 9Designing a Database(Hands-On)ITE 252Database Management

http://www.wiley.com/WileyCDA/WileyTitle/productCd-047011410X.html



Understanding Core Database Concepts

Lesson 1




Database• A database (db) is an organized

collection of data, typically stored in electronic format. – It allows you to input data, organize the

data and retrieve the data quickly. – Traditional databases are organized by

fields, records, and files.




Database Files

• Microsoft SQL server uses three types of files to store the database:– Primary data files, with an .mdf extension,

which contain user-defined objects, such as tables and views, as well as system tables.

– Secondary data files, with an .ndf extension, on separate physical hard disks to give your database more room.

– Transaction log files use an .ldf extension and don’t contain any objects such as tables or views.




Database Management System (DBMS)

• Most users do not access the databases directly, Instead, users use a database management system (DBMS) to access the databases indirectly.

• DBMS is a collection of programs that enables you to enter, organize, and select data in a database.




Database Servers

• Databases are often found on database servers so that they can be accessed by multiple users and to provide a high-level of performance.

• A popular database server is Microsoft SQL Server.




Constraints

• Constraints are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered.




SQL Server Management Studio (SSMS)

• The central feature of SSMS is the Object Explorer, which allows the user to browse, select and manage any of the objects within the server.




SQL Server Management Studio (SSMS)




Data Manipulation Language (DML)• Data Manipulation Language (DML) is the

language element which allows you to use the core statements: – SELECT: Retrieves rows from the database and

enables the selection of one or many rows or columns from one or many tables in SQL Server.

– INSERT: Adds one or more new rows to a table or a view in SQL Server.

– UPDATE: Changes existing data in one or more columns in a table or view.

– DELETE: Removes rows from a table or view.– MERGE: Performs insert, update, or delete

operations on a target table based on the results of a join with a source table.




Data Definition Language (DDL)• Data Definition Language (DDL) is a subset

of the Transact-SQL language.• It deals with creating database objects like

tables, constraints, and stored procedures. • Some DDL commands include:

– USE: Changes the database context.– CREATE: Creates a SQL Server database

object (table, view or stored procedure)– ALTER: Changes an existing object– DROP: Removes an object from the database




System Tables

• System views belong to the sys schema. Some of these system tables include:– sys.Tables– sys.Columns– sys.Databases– sys.Constraints– sys.Views– sys.Procedures– sys.Indexes– sys.Triggers– sys.Objects




Creating Database ObjectsLesson 2




Data Types• A data type is an attribute that specifies the type

of data that an object can hold and it also specifies how many bytes each data type takes up.

• SQL Server 2008’s built-in data types are organized by these general categories:– Exact Numbers– Approximate Numbers– Date and Time– Character Strings– Unicode Character Strings– Binary Strings– Other Data Types– CLR Data Types– Spatial Data Types




Data Types

• Money (Numeric) - This numeric data type is used in places where you want money or currency.

• Datetime - The datetime date and time data type is used to store date and time data in many different formats

• Integer - The int numeric data type is used to store mathematical computations and is used when you do not require a decimal point output.




Data Types

• Varchar - This character string data type is commonly used in databases where you are supporting English attributes– nvarchar – Used for non-English languages

• Boolean - Otherwise known as a bit data type.

• Float - This numeric data type is commonly used in the scientific community and is considered an approximate-number data type.




Data Types




Views

• A view is simply a virtual table consisting of different columns from one or more tables.

• Unlike a table, a view is stored in the database as a query object; therefore, a view is an object that obtains its data from one or more tables.




Stored Procedures

• A stored procedure is a previously written SQL statement which has been “stored” or saved into the database.

• One of the things that will save you time when running the same query over and over again is to create a stored procedure, which you can then execute from within the database’s command environment.




SQL Injections

• A SQL injection is an attack in which malicious code is inserted into strings which are later passed on to instances of SQL Server waiting for parsing and execution.

• Any procedure which constructs SQL statements should be reviewed continually for injection vulnerabilities because SQL Server will execute all syntactically valid queries from any source.




Summary

• A data type is an attribute that specifies the type of data that an object can hold and it also specifies how many bytes each data type takes up.

• As a general rule, if you have two data types that are similar but only differ in how many bytes each data type uses, it has a larger range of values and/or has increased precision.

• SQL Server includes a wide range of pre-defined data types called built-in data types. Most databases that you create or use only need to use these datatypes.




Summary

• Exact numeric data types are the most common SQL Server data types used to store numeric information.

• int is the primary integer (whole number) data type.

• Precision (p) is the maximum total number of decimal digits which could be stored, both to the left and to the right of the decimal point; this value must be a minimum of 1 and a maximum of 38. The default precision number is 18.




Summary

• The entire purpose of a table is to provide structure for storing data within a relational database.

• A view is simply a virtual table consisting of different columns from one or more tables. Unlike a table, a view is stored in the database as a query object; therefore, a view is an object that obtains its data from one or more tables.

• A stored procedure is a previously written SQL statement which has been “stored” or saved into the database.



Third Normal Form

(See figure 4-31 in text; notice DEPARTMENT

table)




Homework

HW#2:• In HW#1 you created an E-R diagram

modeled after the Good Reading Bookstores E-R diagram (Figure 3-19 in your text and slide 14 here) for a publisher of music (limit to MP3s).

• Now apply the normalization technique for all three forms, building tables similar to Figure 4-30 and 4-31 in your text on pages 132-133.

• Due next Monday (try to use Blackboard)


1 nassau community collegeprof. vincent costa acknowledgements: introduction to database management,...

Documents

database management

database db

database servers databases

popular database server

database handson ite

vincent costa acknowledgements

core database concepts

nassau community collegeprof