Lec 5 E-R module

Lec 5 E-R model

Entity Sets

  1. Entity with several attributes is considered to be the basic block of database system.
    • Entity can be either concrete or abstract.
      • Example: specific student, company, event, plant.
    • Attribute types
      • Simple(sex : man or woman) and Composite (name : first-name, middle-name, last-name)
      • Singled-valued(id) and multi-valued(phone number)
      • Derived attributes(in another word,optional attributes, like hobbies, pets and something that's not so necessary)
  2. Entities are connected by "relationships"(will be mentioned in next part).
    • Go to next point for details

Relationship Sets

  1. relationship set is an association among several entities(usually binary)

    • Example:
    RelationSet  CustomerEntity   LoanEntity
    borrower1   Jones                  L-17
    borrower2   Mike                   L-11
        ```
    
    
  2. Binary relationship set types

    • one to one
    • one to many
    • many to one
    • many to many
  3. Keys

    • Super key : a set of some special attributes which uniquely determined each entity

    • Candidate key : minal member of super key

    • Example :
      Candidate key:{cus-num}, {cus-name},{cus-id}
      Super key:{cus-num, cus-name,cus-id}

    • The combination of primary keys participating entity sets forms a super key of relation set [e.g. (customer-id, loan-number) is the super key of borrower]

    • The difference between candidate key and primary key

      • Considering the semantics, we will select one from candidate keys to be the primary key. (?)
      • To be continued
  4. E-R Diagram

    • Basic information

      • Rectangles(長方形) represent entity sets.
      • Diamonds(菱形) represent relationship sets.
      • Lines link attributes to entity sets and entity sets to relationship sets.
      • Ellipses(橢圓) represent attributes.
        • Double ellipses represent multivalued attributes.
        • Dashed ellipses denote derived attributes.
      • Underline indicates primary key attributes

        Screen Shot 2015-05-02 at 7.24.55 PM.png
    • Sometimes there will be a descriptive attribute of the relationship such as "access-date".


      Screen Shot 2015-05-02 at 7.26.25 PM.png
    • Recursive relationship set

      Screen Shot 2015-05-02 at 7.27.10 PM.png

    • Binary relation types:

      -> : to one, -- : to many

      • Example:

        1. Customer <- borrower -> loan

          A loan is associated with at most one customer via borrower

        2. Customer <- borrower -> loan

          A customer is associated with several (including 0) loans via borrower.

        3. Customer -- borrower -> loan

          A customer is associated with at most one loan via borrower.

        4. Customer -- borrower -- loan

          A loan is associated with several (possibly 0) customers via borrower.

      • Total participation(double line) & Patial participation(single line)

      • Binary vs. Non-binary

        • Convert

          parents(he, she, child) => father(he, child), mother(she, child)

Weak Entity Sets

  1. An entity set that does not have a primary key is referred to as a weak entity set.
  2. Instead of primary key, it will have one or more discriminator or partial key.
  3. Weak entity should depend on a strong entity which has a primary key.

Extended E-R Features

  1. Specialization & Generalization

    • Similar to the "herited" concept in oop programming.
    • This should satisfy "disjoint" and "overlaping"
  2. Total and Parital

    • Total: an entity must belong to one of the lower-level entity sets.
    • Partial: an entity need not belong to one of the lower-level entity sets.
  3. Aggregation

    • Similar to the "Encapsulation" in oop programming.
    • Treat relationship as an abstract entity.


      Screen Shot 2015-05-02 at 7.44.37 PM.png

      Screen Shot 2015-05-02 at 7.44.41 PM.png

Design of an E-R Database Schema

Pass

Reduction of an E-R Schema to Tables

  1. A strong entity set => to a table with the same attributes.

  2. Composite attributes are flattened out by creating a separate attribute for each component attribute.

    • customer(customer-id,name, cust-street, cust-city)
    • name(first-name, last-name)

    ==>

    • customer(customer-id, first-name, last-name, cust-street, cust-city)
  3. A multivalued attribute M of an entity E is represented by a separate table.

    • employee(emp-id, ename, sex, age, dependent-names(Derived))

    ==>

    • employee(emp-id, ename, sex, age)
    • employee-dependent-names(emp-id, dependent-name)
  4. Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the one side. (If there is parital participation, there may be some NULL values which we do not care)

    • account(account-number, balance)
    • branch(branch-name, branch-city, assets)
    • account-branch(account-number,_ branch-number_)

    ==>

    • account(account-number, branch-name, balance)
    • branch(branch-name, branch-city, assets)
  5. one-to-one relationship sets, either side can be chosen to act as the “many” side.

  6. The table corresponding to a relationship set linking a weak entity set to its identifying strong entity set is redundant. (Similar to the method mention in 4.)

Representing Specialization

table table attributes
person person-id, name, street, city
customer person-id, name, street, city, credit-rating
employee person-id, name, street, city, salary
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末变过,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌歧焦,老刑警劉巖蟆豫,帶你破解...
    沈念sama閱讀 222,807評(píng)論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件橘忱,死亡現(xiàn)場離奇詭異顷扩,居然都是意外死亡火邓,警方通過查閱死者的電腦和手機(jī)飞崖,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門烂叔,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人固歪,你說我怎么就攤上這事蒜鸡。” “怎么了牢裳?”我有些...
    開封第一講書人閱讀 169,589評(píng)論 0 363
  • 文/不壞的土叔 我叫張陵逢防,是天一觀的道長。 經(jīng)常有香客問我蒲讯,道長忘朝,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,188評(píng)論 1 300
  • 正文 為了忘掉前任判帮,我火速辦了婚禮局嘁,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘晦墙。我一直安慰自己悦昵,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,185評(píng)論 6 398
  • 文/花漫 我一把揭開白布晌畅。 她就那樣靜靜地躺著旱捧,像睡著了一般。 火紅的嫁衣襯著肌膚如雪踩麦。 梳的紋絲不亂的頭發(fā)上枚赡,一...
    開封第一講書人閱讀 52,785評(píng)論 1 314
  • 那天,我揣著相機(jī)與錄音谓谦,去河邊找鬼贫橙。 笑死,一個(gè)胖子當(dāng)著我的面吹牛反粥,可吹牛的內(nèi)容都是我干的卢肃。 我是一名探鬼主播疲迂,決...
    沈念sama閱讀 41,220評(píng)論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢(mèng)啊……” “哼莫湘!你這毒婦竟也來了尤蒿?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,167評(píng)論 0 277
  • 序言:老撾萬榮一對(duì)情侶失蹤幅垮,失蹤者是張志新(化名)和其女友劉穎腰池,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體忙芒,經(jīng)...
    沈念sama閱讀 46,698評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡示弓,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,767評(píng)論 3 343
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了呵萨。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片奏属。...
    茶點(diǎn)故事閱讀 40,912評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖潮峦,靈堂內(nèi)的尸體忽然破棺而出囱皿,到底是詐尸還是另有隱情,我是刑警寧澤忱嘹,帶...
    沈念sama閱讀 36,572評(píng)論 5 351
  • 正文 年R本政府宣布嘱腥,位于F島的核電站,受9級(jí)特大地震影響德谅,放射性物質(zhì)發(fā)生泄漏爹橱。R本人自食惡果不足惜萨螺,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,254評(píng)論 3 336
  • 文/蒙蒙 一窄做、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧慰技,春花似錦椭盏、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至艾帐,卻和暖如春乌叶,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背柒爸。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評(píng)論 1 274
  • 我被黑心中介騙來泰國打工准浴, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人捎稚。 一個(gè)月前我還...
    沈念sama閱讀 49,359評(píng)論 3 379
  • 正文 我出身青樓乐横,卻偏偏與公主長得像求橄,于是被迫代替她去往敵國和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子葡公,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,922評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容

  • **2014真題Directions:Read the following text. Choose the be...
    又是夜半驚坐起閱讀 9,586評(píng)論 0 23
  • 不要用戰(zhàn)術(shù)的勤奮來掩蓋戰(zhàn)略的懶惰罐农。~~在理論上成立催什,在實(shí)踐中證明~~
    小小蟹小小閱讀 170評(píng)論 0 0
  • 大家都知道蛆楞,燈廠的造車技術(shù)不是一般的流弊溯乒。。豹爹。 e-bike電動(dòng)腳踏車 光從外觀上看裆悄,這臺(tái)車酷炫帥氣,未來感十足臂聋,...
    喵個(gè)車閱讀 275評(píng)論 0 4
  • 2015年8月8日立秋光稼,一個(gè)值得紀(jì)念的日子,因?yàn)榻裉煜挛绱蛴鹈虻臅r(shí)候孩等,因?yàn)闆]做好準(zhǔn)備運(yùn)動(dòng)艾君,穿了不合適的鞋等原因,...
    小喵醬醬醬閱讀 363評(píng)論 0 1
  • 【目標(biāo)】2017年輕松喜悅的獲得50萬的財(cái)富 【動(dòng)機(jī)】 我正在運(yùn)用金剛體系這套百分之百行之有效的全新的世界觀來改變...
    韓磊_fb71閱讀 171評(píng)論 0 1