《Database System Concepts（数据库系统概念）》第六版——第六次作业（第七章）

7.3

7.5

• a

  意味着企业模式的个体间是独立的、无联系的
  

• b

  意味着环上的个体间存在着两种不同的联系
  

7.7

在弱实体集变为的强实体集上加入的其标识实体集的主码属性即为产生的冗余属性

7.8

不用
因为section依赖的是某一具体的course（由course_id决定），这便限制了联系的多对一，不需要再对主码和外码再做约束

7.7

在advisor上建立两个外码约束，属性i_ID参考instructor的主码ID，属性s_ID参考student的主码ID,其中s_ID为主码，i_ID为超码

7.20

• a

  author(name)
  publisher(name)
  book(ISBN)
  customer(email)
  shopping_basket(basket_id)
  warehouse(code)
  

• b

• c

7.21

• ER图：略

• 关系模式

  car(VIN)

  dealer(dearler_id,name)

  customer(customer_id,name)

  model(model_id,name)

  brand(brand_name)

  option(option_id)

• 主外约束

  sell(
  primary key (VIN,dealer_id),    
  foreign key VIN references car,
  foreign key dealer_id references dealer    
  )
  modeled(
  primary key (VIN,model_id),    
  foreign key VIN references car,
  foreign key model_id references model    
  )
  optioned(
  primary key (option_id,model_id),    
  foreign key option_id references option,
  foreign key model_id references model    
  )
  ascription(
  primary key (brand_name,model_id),    
  foreign key brand_name references brand,
  foreign key model_id references model    
  )
  belong(
  primary key (customer_id,VIN),    
  foreign key customer_id references customer,
  foreign key VIN references car    
  )
  
  

7.22

• ER图：略

• 关系模式

  customer(customer_id,name,address)

  parcel(parcel_id,deadline)

  location(location_id,location_type,city)

• 主外约束

  send_or_receive(
  primary key (send_id,receive_id,parce_id),    
  foreign key send_id references customer,
  foreign key receive_id references customer,
  foreign key parcel_id references parcel    
  )
  passed(
  primary key (location_id,parcel_id),    
  foreign key location_id references location,
  foreign key parcel_id references parcel    
  )
  
  

7.23

• ER图：略

• 关系模式

  customer(customer_id,name)

  flight(flight_id,capacity)

  flight_schedule(schedule_id,time,route)

• 主外约束

  booking(
  primary key (customer_id,flight_id),    
  foreign key customer_id references customer,
  foreign key flight_id references flight 
  )
  future(
  primary key (flight_id,schedule_id),    
  foreign key flight_id references flight,
  foreign key schedule_id references schedule
  )
  

7.25

course_dept(
foreign key dept_name references department,
foreign key course_id references course    
)
inst_dept(
foreign key dept_name references department,
foreign key ID references instructor   
)
stud_dept(
foreign key dept_name references department,
foreign key ID references student  
)
advisor(
foreign key s_ID references student,
foreign key i_ID references instructor  
)
teaches(
foreign key ID references instructor,
foreign key (course_id,sec_id, semester, year) references sec_course
)
takes(
foreign key ID references student,
foreign key (course_id,sec_id, semester, year) references sec_course
)
sec_course(
foreign key course_id references course
foreign key (sec_id, semester, year) references course
)
sec_time_slot(
foreign key time_slot_id references time_slot
foreign key (course_id,sec_id, semester, year) references sec_course
)
sec_class(
foreign key (building,room_number,capacity) references classroom,
foreign key (course_id,sec_id, semester, year) references sec_course
)