Summer 2017 Name: Group: 2 ID Number: Date: 23 July, 2017 Business Technology-IT

Question

Summer 2017 Name: Group: 2 ID Number: Date: 23 July, 2017 Business Technology-IT111 Unit Assessment-Portfolio Task Il- Databases 1. Create a database file under your Nome and ID - Perttollo !1. You will create two database tables in there; the first one- below - is called Employee (2 marks IDN NameHoursBorn Kuwait Citizen Add New Field Sara Shoug Afrah 4 Manar 16 1/3/198 9 3/10/1se 27 12/31/1979 33 /18/1981 38 6/7/3982 5 Aisha (New) a. Create the first table (careful with the numbers format!).(5 marks-1.0 for each field) b. Define the primary key. (1 marks) c. Enter the data into the Employees table. (5 marks-1.0 for each record) 2. The second table is called: Wages (1 mark) Wages ID Name Wage Sara 2 Shouq Afrah 4 Manar 5 Aisha $7.50 $8.00 $10.50 $11.50 $11.00 a. Create the second table (careful with the numbers format!).(3 marks-1.0 for each field) b. Define the primary key. (1 marks) c. Enter the data into the Wages table. (5 marks-1.0 for each record)

Explanation / Answer

1. a.
create table Employee(
ID number,
Name varchar(20),
Born date,
Hours number,
Kuwait_Citizen char(1) check (Kuwait_Citizen = 'Y' or Kuwait_Citizen = 'N')
);
b. primary key is ID, which is unique.
c.
Insert into Employee
Values(1, 'Sara', 16, Y, '1982-01-01');

You can add the remaining as shown above.

2. a.
create table Wages(
ID number,
Name varchar(20),
Wage Currency
);

b. Primary key is ID,
c.
Insert into Wages
Values(1, 'Sara', 16, '$7.50');
You can add the remaining as shown above.

3. fields and data in both the tables arer shown in the previous two questions,

4. To join two tables a primary key field from one table should be mapped to a foreign key field in the other table.
Here Primary key from the Employees table is ID and the foreign key from the Wages table is ID.
If you are using a SQL Server Management Studio: You can join two tables by following steps
a. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design, The table opens in Table Designer.
b. From the Table Designer menu, Open Relationships.
c. In the Foreign-key Relationships dialog box, click Add.
The relationship appears in the Selected Relationship list with a system-provided name in the format FK_<Wages>_<Wages>,
d. Click the relationship in the Selected Relationship list.
e. Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.
f. In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table (Employee) that will be on the primary-key side of the relationship.
g. In the grid beneath, choose the columns contributing to the table's primary key. In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.
Table Designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.
h. Choose OK to create the relationship.

This creates the relationship between the two tables (Employee and Wages)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.