B using the above schema write the following queries

Info icon This preview shows pages 2–5. Sign up to view the full content.

View Full Document Right Arrow Icon
b. Using the above schema, write the following queries in SQL . i. Find the names of all employees who have a child born on or after January 1, 2000. ii. Find those employees who took an examination for the skill type typing in the city Dayton . iii. List all skill types in the relation Emp . Answer: a. No Answer. b. Queries in SQL . i. Program: select ename from emp as e , e.ChildrenSet as c where ’March’ in ( select birthday . month from c ) ii. Program:
Image of page 2

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Practice Exercises 3 select e.ename from emp as e , e.SkillSet as s , s.ExamSet as x where s.type = ’typing’ and x.city = ’Dayton’ iii. Program: select distinct s.type from emp as e , e.SkillSet as s 22.3 Consider the E-R diagram in Figure 22.5, which contains composite, multivalued, and derived attributes. a. Give an SQL schema definition corresponding to the E-R diagram. b. Give constructors for each of the structured types defined above. Answer: a. The corresponding SQL:1999 schema definition is given below. Note that the derived attribute age has been translated into a method. create type Name ( first name varchar(15) , middle initial char , last name varchar(15) ) create type Street ( street name varchar(15) , street number varchar(4) , apartment number varchar(7) ) create type Address ( street Street , city varchar(15) , state varchar(15) , zip code char(6) ) create table customer ( name Name , customer id varchar(10) , address Adress , phones char(7) array[10] , dob date ) method integer age () b. create function Name ( f varchar (15), m char , l varchar (15)) returns Name begin set first name = f ; set middle initial = m ; set last name = l ; end create function Street ( sname varchar (15), sno varchar (4), ano varchar (7))
Image of page 3
4 Chapter 22 Object-Based Databases returns Street begin set street name = sname ; set street number = sno ; set apartment number = ano ; end create function Address ( s Street , c varchar (15), sta varchar (15), zip varchar (6)) returns Address begin set street = s ; set
Image of page 4

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern