Cơ sở dữ liệu - Chapter 1: Creating databases and database files

Chapter 1Creating Databases and Database Files3/29/20201AgendaIntroduction to databaseCreating databaseManaging databaseMaintainance to database3/29/20202DatabaseDatabase: A collection of data that are related in a meaningful way, which can be accessed in different logical ordersDatabase Management System (DBMS):A general purpose software package designed to store and manage database3/29/20203Simplified database system environment3/29/20204Database exampleA UNIVERSITY database for maintaining information concerning students, courses, and grades in a university environmentWe have:STUDENT file stores data on each studentCOURSE file stores data on each courseSECTION file stores data on each section of each courseGRADE_REPORT file stores the grades that students receive PREREQUISITE file stores the prerequisites 3/29/20205Database example3/29/20206Database example3/29/20207DBMSObjectives of DBMS:Data availability: users can easily access the data.Data integrity: the data available in the database is a reliable dataData security: only authorized users can access the data.Data Independence: the user to store,update, and retrieve data in an efficient manner. 3/29/20208Three-Schema ArchitectureDefines DBMS schemas at three levels:Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. External schemas at the external level to describe the various user views. 3/29/20209Three-Schema Architecture3/29/202010Introduction to SQL Server 2008MS SQL Server is a Relational Database Management System (RDBMS) designed for use in a network environment.RDBMS is system for storing, manipulating data in a series of related table.3/29/202011Introduction to SQL Server 2008MS SQL Server supports 2 computing models:Client/server application model: 3/29/202012Introduction to SQL Server 2008MS SQL Server supports 2 computing models:Distributed computing model: 3/29/202013Introduction to SQL Server 2008SQL 2008 editions3/29/202014Core EditionsSpecialized EditionsFree EditionsEnterpriseStandardWorkgroupWebDeveloperExpressCompact 3.5Enterprise workloads that need redundancy and built-in Business IntelligenceShared data scenarios in departments and small to large businessesRemote offices that need local instances of company dataFor web application hosting Full featured edition for development and testing onlyEntry level database, ideal for learning and ISV redistributionEmbedded database for developing desktop and mobile applicationOverview of database componentData filesTransaction logFilegroupData PagesExtend3/29/202015Overview of database component3/29/202016Overview of database componentData filesPrimary – system objects: database.mdfStored in SQL Server’s data directory “C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\DATA” Secondary – user objects.ndf Optional way to spread your database content over multiple files.3/29/202017Overview of database componentTransaction logRecords data actionUsed for recoveryUse the .ldf file extension3/29/202018Overview of database componentFilegroupa logical grouping of data files that hold all data and database objects defined for the database.Primary filegroup:Made up of the primary data file and any additional user-defined data files.Store all system references for the database including pointers to objects defined in the resource database. 3/29/202019Overview of database componentFilegroupSecondary filegroup:Control over what data is stored in what location. Without user-defined filegroups, all data is stored in the Primary filegroup, so the flexibility and scalability of the database are reduced dramatically.3/29/202020Overview of database componentAll the data from tables and indexes and the metadata that describes that data is organized in storage objects ExtentsData pages.3/29/202021Overview of database componentExtent: Basic unit of data scopefile storage structure that is 64 KB in size.There are two types of extents: mixed extents and uniform extents.Mixed extents: contain pages from more than one object.Uniform extents: contain eight contiguous pages that belong to the same objec3/29/202022Overview of database componentData page: Basic unit of storage management contain data rows from tables3/29/202023Overview of database componentSchema: container for database objectsassign permissions to the objects it contains.Ex: you may create a schema called HumanResource and place all your employee tables and stored procedures into itWithin the schema, objects cannot have duplicate names. However, objects can have the same name if they exist in different schemas.3/29/202024Overview of database componentSchema: A database principal is assigned ownership of a schema.Ex: FredF is created in the AdventureWorks2008 DB and assigned the default schema of Sales. CreditCard table belongs to Sales schemaIf FredF logs in and executes the query: 1. SELECT * FROM CreditCard The content of CreditCard will be returned 2. SELECT * FROM Person  what will be return if Person table doesn’t exist in Sales schema??3/29/202025SQL Server 2008 DatabaseSystem database:Five system databases: master, model, msdb,and tempdbCreated to store system information and support database operations.Resource database is invisible.The other database are visible during normal database operationsUser database: databases that are created by any server login.3/29/202026SQL Server 2008 DatabaseDatabase Planning:determine how much disk space will be required to support the database. Database Planning steps:Take the database prototype (the test or development version) and fill it with an appropriate amount of test data.Check the size of the data file on disk, and then multiply it by 1.5The resulting file size should be sufficient to accommodate the initial data load of the new database with some room to spare3/29/202027SQL Server 2008 DatabasePlanning the size of the transaction log file: How big the average transaction is that will be executedHow often the transactions will take placeWhat is the physical structure of the tables being modified.3/29/202028Creating a databaseCREATE DATABASE Research on PRIMARY(NAME = ResearchPrimary, FILENAME =' D:\SQL\FG\ResearchPrimary.mdf', SIZE = 500MB, MAXSIZE = 700, FILEGROWTH=20);ALTER DATABASE Research on FILEGROUP RESEARCH1(NAME = ResearchPrimary, FILENAME =' D:\SQL\FG\Research1.ndf‘, SIZE = 500MB, MAXSIZE = 700, FILEGROWTH=20);3/29/202029Database snapshotA point-in-time, static, Read Only view of a database. Reflect the point in time when the database is copied.3/29/202030Database snapshotCan not be backed up. Since the snapshot is a combination of data retrieved from the source database and data stored internally, it is impossible to actually back up the snapshot.Database Snapshots cannot be modified.Source databases cannot be dropped while a snapshot exists.Source databases cannot be restored to a point in time prior to the creation of the snapshot while the snapshot exists.3/29/202031Database snapshotThe syntax for the creation of a snapshot :CREATE DATABASE database_snapshot_name ON (NAME = logical_file_name, FILENAME = 'os_file_name') [ ,...n ] AS SNAPSHOT OF source_database_nameEx:CREATE DATABASE AdventurWorks_snapshot ON (NAME = 'AdventureWorks_Data' , FILENAME = 'C:\temp\snapshot_DB.mdf') AS SNAPSHOT OF AdventureWorks;3/29/202032