Multi valued attributes in sql

A database schema with a relation Employee whose attributes are as shown below, with types specified for multi-valued attributes.

Employee = (ename, ChildrenSet multiset(Children), SkillSet mu开发者_Python百科ltiset(Skills))
Children = (name, birthday)
Skills = (type, ExmSet setoff(Exams))
Exams = (year, city)

1. Define the above schema, with appropriate types for each attribute.
2. Find the name of all employees who have a child born on or after January 1, 2000
3. Find those employees who took an examination for the skill type “typing” in the city "Dayton"
4. List all skill types in the relation Employee.

please can u help me.

---

Define the above schema, with appropriate types for each attribute.

Employees
{
    Employee_id, int, PK
    Name, nvarchar(50)        
}
Childs
{
    Child_id, int, PK
    Employee_id, int, FK
    Name, nvarchar(50)
    Birthday, date
}
Skills
{
    Skill_id, int, PK
    Employee_id, int, FK
    Type, nvarchar(50)
}
Exams
{
    Exam_id, int, PK
    Skill_id, int, FK
    Year, int
    City, nvarchar(50)
}

Find the name of all employees who have a child born on or after January 1, 2000

MyDBEntities db = new MyDBEntities();

var result = from e in db.Employees
             join c in db.Childs
             on e.Employee_id equals c.Employee_id
             where e.Birthday >= new DateTime(2000, 1, 1)
             select e;

Find those employees who took an examination for the skill type “typing” in the city "Dayton"

var result = from e in db.Employees
             join s in db.Skills
             on e.Employee_id equals s.Employee_id
             join x in db.Exams
             on s.Skill_id equals x.Skill_id
             where s.Type.Equals("typing") && x.City.Equals("Dayton")
             select e;

List all skill types in the relation Employee

var result = from e in db.Employees
             join s in db.Skills
             on e.Employee_id equals s.Employee_id
             select new EmployeeSkill() 
             {
                 EmployeeName = e.Name,
                 SkillType = s.Type
             };

bottom line... Linq2Sql rules :)

now... you just need to convert Linq to SQL and you have your homework done ;)

(you didn't expected to get all done here, right?)