Database Assignment

Creating a Database

Database Objects

In this database you will create a new database and three tables. You can use either the graphical tools or the sql query window. When you are done, script the database from using the scripting wizard in the tasks menu of the database.

Create the database

Create a new database called "MeetingNotes"

Add a second data file to the database

Creating tables

The first table will be called "Employee". It will have these columns and datatypes:

Column Name Data type constraints Allow Nulls
EmployeeID int (identity 1,1 ) Primary Key  
LastName NVarChar 255    
FirstName NVarChar 255   X;
HireDate Date    

Next create a table called "Meeting"

Column Name Data type constraints Allow Nulls
MeetingID int (identity 1,1 ) Primary Key  
MeetingTitle NVarChar 255    
MeetingPurpose Nvarchar 255    
MeetingDate Date    
MeetingTime Time default 1:00 PM  
MeetingLocation Nvarchar(255)    

Now we will create a third table called "MeetingDetail". Before we do though we will create an XML Schema collection to store an XML Schema that will validate an xml field which stores the meeting's minutes.

Here is the structure of a meetings xml document

<?xml version="1.0" encoding="utf-8"?>
<mn:minutes xmlns:mn="http://www.ITC226.edu/meetingminutes">
	<mn:heading>
		<mn:purpose></mn:purpose>
		<mn:attending></mn:attending>
	</mn:heading>
	<mn:body>
		<mn:narrative></mn:narrative>
		<mn:actionItems>
			<mn:item></mn:item>
			<mn:item></mn:item>
		</mn:actionItems>
	</mn:body>
</mn:minutes>

Here is the schema to use

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:mn="http://www.ITC226.edu/meetingminutes" attributeFormDefault="unqualified" 
elementFormDefault="qualified" targetNamespace="http://www.ITC226.edu/meetingminutes" 
xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="minutes">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="heading">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="purpose" />
							<xs:element name="attending" />
						</xs:sequence>
					</xs:complexType>
				</xs:element>
				<xs:element name="body">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="narrative" />
							<xs:element name="actionItems">
								<xs:complexType>
									<xs:sequence>
										<xs:element 
maxOccurs="unbounded" name="item" />
									</xs:sequence>
								</xs:complexType>
							</xs:element>
						</xs:sequence>
					</xs:complexType>
				</xs:element>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>

So here is the table

Column Name Data type constraints Allow Nulls
MeetingDetailID int (identity 1,1 ) Primary Key  
MeetingID INT Foreign Key  
MeetingMinutes XML minutesschema  

when you have the database done, script its objects and email the script to me

an