1 / 16

Database Management

Database Management. Database Administration (DBA). The DBA’s tasks will include the following: The design of the database. After the initial design, the DBA must monitor performance and, if problems surface, changes must be made to the database structure.

taima
Download Presentation

Database Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Management

  2. Database Administration (DBA) The DBA’s tasks will include the following: • The design of the database. After the initial design, the DBA must monitor performance and, if problems surface, changes must be made to the database structure. • Keeping users informed of changes in the database structure that will affect theme.g. if the size or format of a particular field is altered or additional fields added. ICT5

  3. DBA (continued) • Maintenance of the data dictionary for the databaseand responsibility for establishing conventions for naming tables, columns, indexes and so on. • Implementing access privileges for all usersspecifying which items can be accessed and/or changed by each user. • Allocating passwords to each user • Providing training to users in how to access and use the database ICT5

  4. The data dictionary The data dictionary is a ‘database about the database’. It will contain information such as: • Which tables and columns are included in the present structure; • The names of the current tables and columns; • The characteristics of each item of data, such as its length and data type; • Any restrictions on the value of certain columns; • The meaning of any data fields that are not self-evident; e.g. a field such as ‘course type’; • The relationships between items of data; • Which programs access which items of data, and whether they merely readthe data or change it ICT5

  5. Database Management System (DBMS) • The DBMS is an application program that provides an interface between the operating system and the user in order to make access to the data as simple as possible. It has several other functions as well, and these are described below. • Data storage, retrieval and updateThe DBMS must allow users to store, retrieve and update information as easily as possible, without having to be aware of the internal structure of the database. • Creation and maintenance of the data dictionary • Managing the facilities for sharing the databaseThe DBMS has to ensure that problems do not arise when two people simultaneously access a record and try to update it. • Backup and recoveryThe DBMS must provide the ability to recover the database in the event of system failure. • SecurityThe DBMS must handle password allocation and checking, and the ‘view’ of the database that a given user is allowed. ICT5

  6. Querying the database • Different database systems all have their own way of performing queries to extract data. However all perform similar functions, allowing the user to: • Combine into one table the information from two or more related tables • Select the fields to be shown in the ‘Answer’ table • Specify criteria for searching one.g. find the names and addresses of all club members whose subscriptions are due • Save the query so that it can be executed whenever necessary • Save the ‘Answer’ table so that it can be displayed or used as the basis for a report or a “mail shot”, for example ICT5

  7. Querying the database e.g. • Query by Example from the Northwind Database ICT5

  8. Structured Query Language (SQL) • The basic retrieval facility in SQL is the selectstatement, which consists of three clauses in the general form select.... from .... where .... The select clause specifies columns to be extracted from the table(s) or relation(s) in the from clause. The where clause specifies the condition that must be met for items to be selected. ICT5

  9. SQL Example • For example, to find all the female students in the STUDENTS table seen before, we could enter: SELECT tblStudents.StudentID, tblStudents.Surname, tblStudents.Forename, tblStudents.DateOfBirth, tblStudents.Sex FROM tblStudents WHERE (((tblStudents.Sex)="F")); ICT5

  10. Querying the database e.g. • Query by Example from the Northwind Database in SQL view SELECT [Product List].ProductID, [Product List].ProductName FROM Products AS [Product List] WHERE ((([Product List].Discontinued)=No)) ORDER BY [Product List].ProductName; ICT5

  11. Querying the database e.g. • A more complicated query from Northwind Database SELECT DISTINCTROW Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia; ICT5

  12. Using indexes • A DBMS will allow the application developerto create an index for any field in the database, whether or not that field is unique. • All the indexes are then held in memory while the database is open to allow fast retrieval of data. ICT5

  13. Indexes – example • For example, suppose the following records were added in the sequence shown by the record number: Record # Student ID Surname Firstname1 5321 Bates Joseph2 1963 Scully Anne3 4218 Chatterjee Sara4 3727 Fidler Lillian5 2858 Deacon Michael • If the Student ID field is indexed, the index will have entries as follows: Student ID Record # 1963 2 2858 5 3727 442183 5321 1 ICT5

  14. Why index? • Indexing a particular field will speed up access to data, but will slow down record updating since every time an indexed field is changed, the index entry will have to be changed as well. • When a new record is added, all indexes have to be updated. • It makes sense, in general, to index any foreign keys in a table, and any fields like surname or department that you frequently need in alphabetical order in a report. • It is also a good idea to index fields that you often use in a query criteria; e.g. to quickly find all club members whose subscriptions are overdue, the Due Date field needs to be indexed. • Indexed fields other than the primary key field are known as secondary keys. ICT5

  15. Client-Server Database • Modern databases often have to operate on a network • This would be a client / server operation • DMBS server software processes requests from DBMS client software on network workstations • Process results can be sent from server to client for further processing if required ICT5

  16. Client-server database advantages • An expensive resource available to large number of users • Client stations can, with permission, update the server database • Consistency is maintained because only one copy of database (on server) • Client sends query, server does processing, results returned to client • Communications time between server and client is minimised because only results are sent back to client • Appropriate programs and report formats can be held on client workstations for particular departments ICT5

More Related