TE RI AL Databases MA Most Visual Basic 2005 applications that you write use data in some form or fashion. Where you retrieve that data from depends on what your application is doing. One of the most common types of applications that you are likely to write is a database application, which retrieves and processes data from a database.
Chapter 1 Access Databases Access databases are common and can be found on most computers, especially if the sample databases were installed along with Microsoft Access as a standalone product or as part of Microsoft Office. People use Access databases for a variety of reasons but mainly because they are standalone databases, meaning that you can create an Access database and then send that database to someone else who, if he or she has Microsoft Access installed, can open and use your database.
Databases Name and Last Name. These two fields in a single record describe the name of a single person, as illustrated in Figure 1-1. Figure 1-1 Queries A query in a database is a group of Structured Query Language (SQL) statements that enable you to retrieve and update data in your tables. Queries can be used to select or update all the data in one or more tables or to select or update specific data in one or more tables. SQL enables you to insert, update, and delete data in a database.
Chapter 1 SQL Server is a relational database consisting of many components, each of which contains multiple objects. In the following sections, you examine the main objects that make up a SQL Server database. While you will not examine each and every object of a database, rest assured that what you do learn here will serve you well, laying the foundation for what you will be doing throughout the rest of this book. A SQL Server database consists of at least two files: a data file and a log file.
Databases Object Description User-Defined Functions A group of SQL statements that can be encapsulated into a subroutine that can be called by views and stored procedures. User-Defined Data Types User-defined data types are based on system data types and enable you to create a data type with attributes that can be applied to all your tables. User Identifies a user with a database.
Chapter 1 do so, you must first delete the row of data containing the foreign key or update the column using a NULL value. Only then are you able to delete the row containing the primary key. Referential integrity is based on the relationship between foreign and primary keys and ensures that key values are consistent across all tables.
Databases Second, stored procedures are similar to procedures and functions in other programming languages, as they can contain input and output parameters and can return values. They use logic to control the flow of processing, and numerous functions and SQL statements can be used in stored procedures. You can use stored procedures to execute routine functions, such as selecting, inserting, updating, and deleting data.
Chapter 1 Oracle consists of many components in addition to the database engine, including components that perform data analysis, help you manage XML and image data, manage applications and clusters, and monitor and manage database performance. However, those components are beyond the scope of this book, which focuses on the components that make up an Oracle database. Because Oracle is a relational database, it contains numerous components, and each component contains many objects.
Databases Tables Tables in Oracle perform the same function as they do in SQL Server — they contain information about your business. Keys Keys in Oracle perform the same function as they do in SQL Server — they uniquely identify each row of data in a table. Indexes Indexes in Oracle perform the same function as they do in SQL Server — they provide efficient access to the data in your tables. However, Oracle contains many different kinds of indexes, as outlined in this section.
Chapter 1 Stored procedures A stored procedure in Oracle is functionally equivalent to a stored procedure in SQL Server and stores a single or group of SQL statements compiled into an execution plan. Views Views in Oracle perform the same function as they do in SQL Server and are like virtual tables containing data from one or more tables.
Databases Each table in your database represents an object about your business, and each column in a table represents an attribute of the object that the table represents. A row in the table represents a unique entry for the object that the table defines. To design a relational database, you must first identify all of the objects that will make up your database. The term object is used to represent a set of information. You can also use the term entity in place of object.
Chapter 1 You’ll also notice that the field names have been defined using Pascal casing. Pascal casing is where the first letter of each word is in uppercase, such as FirstName. You can choose to use a field name with spaces in it, such as First Name, or with an underscore in it, such as First_Name. Whichever method you choose to use is fine.
Databases ❑ The first normal form eliminates repeating groups of data in a table. You create a separate table for each set of related data and identify each table with a primary key, which uniquely identifies each row of data. ❑ The second normal form creates separate tables for sets of values that apply to multiple records, and relates these tables with foreign keys. ❑ The third normal form eliminates columns that do not depend on the primary key.
Chapter 1 Second normal form The rule for the second normal form dictates that you must create separate tables for sets of values that apply to multiple records and in multiple tables, and relate these tables with foreign keys. Starting with the Employee table, you can see that multiple employees can work at the same location, as well as have the same job title.
Databases Building the Case Study Databases In the rest of this book, you use some sample databases to work through the exercises in the chapters. These sample databases should be available to you either on your machine or another machine on your network and depend on whether you have SQL Server and/or Oracle installed. Starting in Chapter 4, you use an Access database that you either create or download from the Wrox Web site for this book.
ProjectID ProjectName ProjectDescription SequenceNumber LastUpdateDate Projects GroupID GroupName GroupDescription LastUpdateDate Groups Number(Replication ID) Text(50) Memo Number(Byte) Date/Time Number(Replication ID) Text(50) Memo Date/Time Not Null Not Null Null Not Null Not Null Not Null Not Null Null Not Null GroupProjectsID GroupID ProjectID GroupProjects Number(Replication ID) Number(Replication ID) Number(Replication ID) Not Null Not Null Not Null Chapter 1 Figure 1-6 If you choose
Databases Try It Out Creating the ProjectTimeTracker Access Database 1. Start Microsoft Access by clicking Start on the taskbar and then clicking Run. 2. In the Run dialog box, enter MSACCESS and then click OK. 3. Depending on which version of Microsoft Access you have, you may see the Microsoft Access dialog box prompting you to create a new database or open an existing database. Or you may see the New File window docked on the right-hand side of Microsoft Access.
Chapter 1 Table 1-6: Projects Table Fields Field Name Data Type Data Type Attributes ProjectID Number Field Size = Replication ID, Required = Yes, Indexed = Yes (No Duplicates) ProjectName Text Field Length = 50, Required = Yes, Allow Zero Length = No ProjectDescription Memo Required = No, Allow Zero Length = No SequenceNumber Number Field Size = Byte, Required = Yes LastUpdateDate Date/Time Required = Yes 11.
Databases 19. In the Show Table dialog box, select all three tables and click the Add button. Then click the Close button to close the Show Table dialog box. 20. The Relationships designer now shows all three tables. Click the GroupID column in the GroupProjects table and drag it over to the Groups table and drop it on the GroupID column. This causes the Edit Relationships dialog box to be displayed, as shown in Figure 1-7. Figure 1-7 21. 22.
Chapter 1 SQL Server schema The Access schema was small compared to the SQL Server schema shown in Figure 1-8. You will be doing a limited amount of work on the Project Time Tracker application in Access. Most of your development of this application will be done in either SQL Server or Oracle. Therefore, the schema for SQL Server and Oracle will be larger and comprise all the tables that make up the ProjectTimeTracker database.
Databases Projects ProjectID ProjectName ProjectDescription SequenceNumber LastUpdateDate UniqueIdentifier VarChar(50) Text TinyInt DateTime Not Null Not Null Null Not Null Not Null GroupProjects GroupProjectsID GroupID ProjectID Groups GroupID GroupName GroupDescription LastUpdateDate UniqueIdentifier VarChar(50) Text DateTime Not Null Not Null Null Not Null Users UserID LoginName Password FirstName LastName Email Phone Status GroupID RoleID ManagerID LastUpdateDate UniqueIdentifier VarChar(15) VarC
Chapter 1 4. Click Change next to the Data Source field and in the Change Data Source dialog box, select Microsoft SQL Server and then click OK. 5. In the Add Connection dialog box, select the name of the computer running the SQL Server instance that contains your ProjectTimeTracker database in the Server name field. 6.
Databases 14. At this point, all of your tables have been created and their primary keys have been set. You need to add the foreign key relationships between your tables. Double-click the GroupProjects table. When the Table Designer is displayed, click the Table Designer menu and choose Relationships or click the Relationships icon on the toolbar. The Foreign Key Relationships dialog box is displayed and this is where you define the primary and foreign relationships between your tables. 15.
Chapter 1 17. Click the Add button in the Foreign Key Relationships dialog box to add another relationship. In the Properties window, click the Tables and Columns Specification property and then click the elipse button for this property to display the Tables and Columns dialog box. 18. In the Tables and Columns dialog box, select the Projects table in the Primary key table combo box and then click in the empty row under this combo box and select ProjectID in the combo box that is displayed.
Databases Oracle schema The Oracle schema, shown in Figure 1-12, is similar to the SQL Server schema shown in Figure 1-8. The only differences between these schemas are the data types. Every database vendor has its own implementation for data types and the differences are usually subtle. Therefore, there’s no need to cover the details of the schema again. Refer to Appendix A for a complete data type conversion between the different databases.
Chapter 1 Projects ProjectID ProjectName ProjectDescription SequenceNumber LastUpdateDate Char(36) VarChar2(50) Clob Number(3) Date Not Null Not Null Null Not Null Not Null GroupProjects GroupProjectID GroupID ProjectID Groups GroupID GroupName GroupDescription LastUpdateDate Char(36) VarChar2(50) Clob Date Char(36) VarChar2(15) VarChar2(75) VarChar2(30) VarChar2(30) VarChar2(50) VarChar2(20) Number(1) Char(36) Char(36) Char(36) Date Not Null Not Null Not Null Not Null Not Null Not Null Not Null Not N
Databases Try It Out Creating the ProjectTimeTracker Oracle Database 1. Start SQL Plus or your favorite third-party tool for running SQL scripts against an Oracle database. 2.
Chapter 1 Ranking number(3) NOT NULL, LastUpdateDate date NOT NULL ); CREATE TABLE TimeSheets ( TimeSheetID char(36) NOT NULL, UserID char(36) NOT NULL, WeekEndingDate date NOT NULL, Submitted number(1) NOT NULL, ApprovalDate date NULL, ManagerID char(36) NULL, LastUpdateDate date NOT NULL ); CREATE TABLE TimeSheetItems ( TimeSheetItemID char(36) NOT NULL, TimeSheetID char(36) NOT NULL, ProjectID char(36) NOT NULL, Hours number(3) NOT NULL, TimeSheetDate date NOT NULL ); 3.
Databases ( CONSTRAINT PK_TimeSheets PRIMARY KEY (TimeSheetID) ); ALTER TABLE TimeSheetItems ADD ( CONSTRAINT PK_TimeSheetItems PRIMARY KEY (TimeSheetItemID) ); 4.
Chapter 1 Summar y This chapter has been quite diverse, covering the major components that make up an Access, SQL Server, and Oracle database. If you weren’t already familiar with them, you should now understand the components of each of these databases and how they tie together to comprise a single database. You should also have gained a deeper appreciation for each of these databases and know which database will meet your business requirements for future projects that you may work on.
