8.4

Consider a database schema with a relation Emp whose attributes are as shown below, with types specified for multivalued attributes.

Emp = (ename, ChildrenSet multiset(Children), SkillSet multiset(Skills))
Children = (name, birthday)
Skills = (type, ExamSet setof(Exams))
Exams = (year, city)

Define the above schema in SQL, using the SQL Server table type syntax from Section 8.2.1.1 to declare multiset attributes.

---

Using the SQL Server table type syntax from Section 8.2.1.1.

CREATE TYPE children AS TABLE (
    name VARCHAR(30), 
    birthday DATE
);

CREATE TYPE exams AS TABLE ( 
    year INTEGER, 
    city VARCHAR(30)
);

CREATE TYPE skills AS TABLE (
    type VARCHAR(30), 
    examset exams
);

CREATE TABLE emp ( 
    ename VARCHAR(30), 
    childrenset children, 
    skillset skills
);

To be honest I haven’t tried the above code on an instance of SQL Server.

The following is a similar code to the one above, but for Postgresql.

CREATE TYPE children AS (
    name VARCHAR(30), 
    birthday DATE
);

CREATE TYPE exams AS ( 
    year INTEGER, 
    city VARCHAR(30)
);

CREATE TYPE skills AS (
    type VARCHAR(30), 
    examset exams[]
);

CREATE TABLE emp ( 
    ename VARCHAR(30), 
    childrenset children[], 
    skillset skills[]
);

If you are interested to see, how we insert into emp, the following code shows for postgresql:-

INSERT INTO emp VALUES ( 
    'Elliot Alderson', 
    ARRAY[
        ('child1', '2000-01-01')::children, 
        ('child2', '2017-09-10')::children
    ],
    ARRAY[
        ('programming', ARRAY[
            (2020,'NY')::exams, 
            (2021,'AA')::exams
        ])::skills,
        ('KCNA',ARRAY[
            (2022,'NY')::exams, 
            (2022,'LA')::exams
        ])::skills
    ]
);